Writing own formula
 

My objective is to create a file of consolidation of identical files, but
with different values in the cells. So I have to maintain the formulas and
sum the values.

The problem is that I created my own formula as the code bellow (onglet is
the sheetname) and Excel writes in the cells the formula itself, it does not
calculate it as I expected. For exemple, it returns:

+Plan1!+Plan2! and not the value of the sum of the 2 cells.

What should I do???

Thanks in advance.

Daniel (Brazil)



Dim formula As Variant

Sheets("Input").Select

For a = 2 To 3
If Cells(a, 1).Value = True Then
Onglet = Cells(a, 3).Value
formula = formula & "+" & Onglet & "!" & RC

End If
Next a

Sheets("Conso").Select
Cells.Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.FormulaR1C1 = formula

End Sub

Writing own formula
 

Hi
Try
Selection.FormulaR1C1 = "=" & formula

regards
Paul

On Jun 18, 9:53 am, dspilberg
wrote:
My objective is to create a file of consolidation of identical files, but
with different values in the cells. So I have to maintain the formulas and
sum the values.

The problem is that I created my own formula as the code bellow (onglet is
the sheetname) and Excel writes in the cells the formula itself, it does not
calculate it as I expected. For exemple, it returns:

+Plan1!+Plan2! and not the value of the sum of the 2 cells.

What should I do???

Thanks in advance.

Daniel (Brazil)

Dim formula As Variant

Sheets("Input").Select

For a = 2 To 3
If Cells(a, 1).Value = True Then
Onglet = Cells(a, 3).Value
formula = formula & "+" & Onglet & "!" & RC

End If
Next a

Sheets("Conso").Select
Cells.Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.FormulaR1C1 = formula

End Sub

Writing own formula
 

Thanks Paul, but it still doesn't work.
It shows the message error '1004' (my excel is in portuguese and i don't
what the message would be in english).
Regards!

" wrote:

Hi
Try
Selection.FormulaR1C1 = "=" & formula

regards
Paul

On Jun 18, 9:53 am, dspilberg
wrote:
My objective is to create a file of consolidation of identical files, but
with different values in the cells. So I have to maintain the formulas and
sum the values.

The problem is that I created my own formula as the code bellow (onglet is
the sheetname) and Excel writes in the cells the formula itself, it does not
calculate it as I expected. For exemple, it returns:

+Plan1!+Plan2! and not the value of the sum of the 2 cells.

What should I do???

Thanks in advance.

Daniel (Brazil)

Dim formula As Variant

Sheets("Input").Select

For a = 2 To 3
If Cells(a, 1).Value = True Then
Onglet = Cells(a, 3).Value
formula = formula & "+" & Onglet & "!" & RC

End If
Next a

Sheets("Conso").Select
Cells.Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.FormulaR1C1 = formula

End Sub

Writing own formula
 

Hi
I don't know what your selection is but try

For each cell in Selection
cell.FormulaR1C1 = "=" & formula
next cell

regards
Paul

On Jun 18, 10:27 am, dspilberg
wrote:
Thanks Paul, but it still doesn't work.
It shows the message error '1004' (my excel is in portuguese and i don't
what the message would be in english).
Regards!



" wrote:
Hi
Try
Selection.FormulaR1C1 = "=" & formula

regards
Paul

On Jun 18, 9:53 am, dspilberg
wrote:
My objective is to create a file of consolidation of identical files, but
with different values in the cells. So I have to maintain the formulas and
sum the values.

The problem is that I created my own formula as the code bellow (onglet is
the sheetname) and Excel writes in the cells the formula itself, it does not
calculate it as I expected. For exemple, it returns:

+Plan1!+Plan2! and not the value of the sum of the 2 cells.

What should I do???

Thanks in advance.

Daniel (Brazil)

Dim formula As Variant

Sheets("Input").Select

For a = 2 To 3
If Cells(a, 1).Value = True Then
Onglet = Cells(a, 3).Value
formula = formula & "+" & Onglet & "!" & RC

End If
Next a

Sheets("Conso").Select
Cells.Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.FormulaR1C1 = formula

End Sub- Hide quoted text -

- Show quoted text -

Writing own formula
 

Same error.

Thanks anyway!

" wrote:

Hi
I don't know what your selection is but try

For each cell in Selection
cell.FormulaR1C1 = "=" & formula
next cell

regards
Paul

On Jun 18, 10:27 am, dspilberg
wrote:
Thanks Paul, but it still doesn't work.
It shows the message error '1004' (my excel is in portuguese and i don't
what the message would be in english).
Regards!



" wrote:
Hi
Try
Selection.FormulaR1C1 = "=" & formula

regards
Paul

On Jun 18, 9:53 am, dspilberg
wrote:
My objective is to create a file of consolidation of identical files, but
with different values in the cells. So I have to maintain the formulas and
sum the values.

The problem is that I created my own formula as the code bellow (onglet is
the sheetname) and Excel writes in the cells the formula itself, it does not
calculate it as I expected. For exemple, it returns:

+Plan1!+Plan2! and not the value of the sum of the 2 cells.

What should I do???

Thanks in advance.

Daniel (Brazil)

Dim formula As Variant

Sheets("Input").Select

For a = 2 To 3
If Cells(a, 1).Value = True Then
Onglet = Cells(a, 3).Value
formula = formula & "+" & Onglet & "!" & RC

End If
Next a

Sheets("Conso").Select
Cells.Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.FormulaR1C1 = formula

End Sub- Hide quoted text -

- Show quoted text -

Writing own formula
 

Hi
What does the 23 refer to in your SpecialCells? The value normally
refers to text, numbers, true/false or errors.
regards
Paul
On Jun 18, 11:19 am, dspilberg
wrote:
Same error.

Thanks anyway!



" wrote:
Hi
I don't know what your selection is but try

For each cell in Selection
cell.FormulaR1C1 = "=" & formula
next cell

regards
Paul

On Jun 18, 10:27 am, dspilberg
wrote:
Thanks Paul, but it still doesn't work.
It shows the message error '1004' (my excel is in portuguese and i don't
what the message would be in english).
Regards!

" wrote:
Hi
Try
Selection.FormulaR1C1 = "=" & formula

regards
Paul

On Jun 18, 9:53 am, dspilberg
wrote:
My objective is to create a file of consolidation of identical files, but
with different values in the cells. So I have to maintain the formulas and
sum the values.

The problem is that I created my own formula as the code bellow (onglet is
the sheetname) and Excel writes in the cells the formula itself, it does not
calculate it as I expected. For exemple, it returns:

+Plan1!+Plan2! and not the value of the sum of the 2 cells.

What should I do???

Thanks in advance.

Daniel (Brazil)

Dim formula As Variant

Sheets("Input").Select

For a = 2 To 3
If Cells(a, 1).Value = True Then
Onglet = Cells(a, 3).Value
formula = formula & "+" & Onglet & "!" & RC

End If
Next a

Sheets("Conso").Select
Cells.Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.FormulaR1C1 = formula

End Sub- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Writing own formula
 

It would seem that you are not using "Option Explicit" in all you modules.
Add that, then see the error concerning RC.

NickHK

"dspilberg" wrote in message
...
My objective is to create a file of consolidation of identical files, but
with different values in the cells. So I have to maintain the formulas and
sum the values.

The problem is that I created my own formula as the code bellow (onglet is
the sheetname) and Excel writes in the cells the formula itself, it does
not
calculate it as I expected. For exemple, it returns:

+Plan1!+Plan2! and not the value of the sum of the 2 cells.

What should I do???

Thanks in advance.

Daniel (Brazil)



Dim formula As Variant

Sheets("Input").Select

For a = 2 To 3
If Cells(a, 1).Value = True Then
Onglet = Cells(a, 3).Value
formula = formula & "+" & Onglet & "!" & RC

End If
Next a

Sheets("Conso").Select
Cells.Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.FormulaR1C1 = formula

End Sub

Writing own formula
 

Hi
The 23 seems Ok - probably a combination of the values allowed
(16+4+2+1 ??). Have you checked that

Selection.SpecialCells(xlCellTypeConstants, 23).Select

is not empty (which would happen if your selection was a set of empty
cells)?
regards
Paul

On Jun 18, 11:34 am, wrote:
Hi
What does the 23 refer to in your SpecialCells? The value normally
refers to text, numbers, true/false or errors.
regards
Paul
On Jun 18, 11:19 am, dspilberg
wrote:



Same error.

Thanks anyway!

" wrote:
Hi
I don't know what your selection is but try

For each cell in Selection
cell.FormulaR1C1 = "=" & formula
next cell

regards
Paul

On Jun 18, 10:27 am, dspilberg
wrote:
Thanks Paul, but it still doesn't work.
It shows the message error '1004' (my excel is in portuguese and i don't
what the message would be in english).
Regards!

" wrote:
Hi
Try
Selection.FormulaR1C1 = "=" & formula

regards
Paul

On Jun 18, 9:53 am, dspilberg
wrote:
My objective is to create a file of consolidation of identical files, but
with different values in the cells. So I have to maintain the formulas and
sum the values.

The problem is that I created my own formula as the code bellow (onglet is
the sheetname) and Excel writes in the cells the formula itself, it does not
calculate it as I expected. For exemple, it returns:

+Plan1!+Plan2! and not the value of the sum of the 2 cells.

What should I do???

Thanks in advance.

Daniel (Brazil)

Dim formula As Variant

Sheets("Input").Select

For a = 2 To 3
If Cells(a, 1).Value = True Then
Onglet = Cells(a, 3).Value
formula = formula & "+" & Onglet & "!" & RC

End If
Next a

Sheets("Conso").Select
Cells.Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.FormulaR1C1 = formula

End Sub- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Writing own formula
 

Paul,

I think the 23 is ok, because I usually use it (it means the selection of
all formulas: numbers, text, errors and logics).

And the selection should never be a set of empty cells (I verified that)
because i go to a sheet and chose cells.select .

Thanks once more!




" wrote:

Hi
The 23 seems Ok - probably a combination of the values allowed
(16+4+2+1 ??). Have you checked that

Selection.SpecialCells(xlCellTypeConstants, 23).Select

is not empty (which would happen if your selection was a set of empty
cells)?
regards
Paul

On Jun 18, 11:34 am, wrote:
Hi
What does the 23 refer to in your SpecialCells? The value normally
refers to text, numbers, true/false or errors.
regards
Paul
On Jun 18, 11:19 am, dspilberg
wrote:



Same error.

Thanks anyway!

" wrote:
Hi
I don't know what your selection is but try

For each cell in Selection
cell.FormulaR1C1 = "=" & formula
next cell

regards
Paul

On Jun 18, 10:27 am, dspilberg
wrote:
Thanks Paul, but it still doesn't work.
It shows the message error '1004' (my excel is in portuguese and i don't
what the message would be in english).
Regards!

" wrote:
Hi
Try
Selection.FormulaR1C1 = "=" & formula

regards
Paul

On Jun 18, 9:53 am, dspilberg
wrote:
My objective is to create a file of consolidation of identical files, but
with different values in the cells. So I have to maintain the formulas and
sum the values.

The problem is that I created my own formula as the code bellow (onglet is
the sheetname) and Excel writes in the cells the formula itself, it does not
calculate it as I expected. For exemple, it returns:

+Plan1!+Plan2! and not the value of the sum of the 2 cells.

What should I do???

Thanks in advance.

Daniel (Brazil)

Dim formula As Variant

Sheets("Input").Select

For a = 2 To 3
If Cells(a, 1).Value = True Then
Onglet = Cells(a, 3).Value
formula = formula & "+" & Onglet & "!" & RC

End If
Next a

Sheets("Conso").Select
Cells.Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.FormulaR1C1 = formula

End Sub- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Writing own formula
 

Nick, I added the "Option Explicit", declared all the variables and now I
have the error in RC. And now? Any ideas?

Thanks!

Nick, how can I use the Option Explicit mode?

"NickHK" wrote:

It would seem that you are not using "Option Explicit" in all you modules.
Add that, then see the error concerning RC.

NickHK

"dspilberg" wrote in message
...
My objective is to create a file of consolidation of identical files, but
with different values in the cells. So I have to maintain the formulas and
sum the values.

The problem is that I created my own formula as the code bellow (onglet is
the sheetname) and Excel writes in the cells the formula itself, it does
not
calculate it as I expected. For exemple, it returns:

+Plan1!+Plan2! and not the value of the sum of the 2 cells.

What should I do???

Thanks in advance.

Daniel (Brazil)



Dim formula As Variant

Sheets("Input").Select

For a = 2 To 3
If Cells(a, 1).Value = True Then
Onglet = Cells(a, 3).Value
formula = formula & "+" & Onglet & "!" & RC

End If
Next a

Sheets("Conso").Select
Cells.Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.FormulaR1C1 = formula

End Sub

Writing own formula
 

Maybe this will get you closer:
formula = formula & "+" & Onglet & "!RC"


dspilberg wrote:

Nick, I added the "Option Explicit", declared all the variables and now I
have the error in RC. And now? Any ideas?

Thanks!

Nick, how can I use the Option Explicit mode?

"NickHK" wrote:

It would seem that you are not using "Option Explicit" in all you modules.
Add that, then see the error concerning RC.

NickHK

"dspilberg" wrote in message
...
My objective is to create a file of consolidation of identical files, but
with different values in the cells. So I have to maintain the formulas and
sum the values.

The problem is that I created my own formula as the code bellow (onglet is
the sheetname) and Excel writes in the cells the formula itself, it does
not
calculate it as I expected. For exemple, it returns:

+Plan1!+Plan2! and not the value of the sum of the 2 cells.

What should I do???

Thanks in advance.

Daniel (Brazil)



Dim formula As Variant

Sheets("Input").Select

For a = 2 To 3
If Cells(a, 1).Value = True Then
Onglet = Cells(a, 3).Value
formula = formula & "+" & Onglet & "!" & RC

End If
Next a

Sheets("Conso").Select
Cells.Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.FormulaR1C1 = formula

End Sub




--

Dave Peterson

Writing own formula
 

Nick,

thanks! It is working well.

Now I have another question.

Can I test the command

Selection.SpecialCells(xlCellTypeConstants, 23).Select

and only continue if this command returns a selection. Or use an On Error
Resume Next and tell VBA to go 3 lines lower in the code, which I don't know
how to do?

Thanks a lot in advance!

Daniel (Brazil)

"dspilberg" wrote:

Nick, I added the "Option Explicit", declared all the variables and now I
have the error in RC. And now? Any ideas?

Thanks!

Nick, how can I use the Option Explicit mode?

"NickHK" wrote:

It would seem that you are not using "Option Explicit" in all you modules.
Add that, then see the error concerning RC.

NickHK

"dspilberg" wrote in message
...
My objective is to create a file of consolidation of identical files, but
with different values in the cells. So I have to maintain the formulas and
sum the values.

The problem is that I created my own formula as the code bellow (onglet is
the sheetname) and Excel writes in the cells the formula itself, it does
not
calculate it as I expected. For exemple, it returns:

+Plan1!+Plan2! and not the value of the sum of the 2 cells.

What should I do???

Thanks in advance.

Daniel (Brazil)



Dim formula As Variant

Sheets("Input").Select

For a = 2 To 3
If Cells(a, 1).Value = True Then
Onglet = Cells(a, 3).Value
formula = formula & "+" & Onglet & "!" & RC

End If
Next a

Sheets("Conso").Select
Cells.Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.FormulaR1C1 = formula

End Sub

Writing own formula
 

Dim rng as Range
On error resume next
set rng = Selection.SpecialCells(xlCellTypeConstants, 23)
On error goto 0
if not rng is nothing then
rng.select
' code for a successful selection
else


end if

--
Regards,
Tom Ogilvy


"dspilberg" wrote:

Nick,

thanks! It is working well.

Now I have another question.

Can I test the command

Selection.SpecialCells(xlCellTypeConstants, 23).Select

and only continue if this command returns a selection. Or use an On Error
Resume Next and tell VBA to go 3 lines lower in the code, which I don't know
how to do?

Thanks a lot in advance!

Daniel (Brazil)

"dspilberg" wrote:

Nick, I added the "Option Explicit", declared all the variables and now I
have the error in RC. And now? Any ideas?

Thanks!

Nick, how can I use the Option Explicit mode?

"NickHK" wrote:

It would seem that you are not using "Option Explicit" in all you modules.
Add that, then see the error concerning RC.

NickHK

"dspilberg" wrote in message
...
My objective is to create a file of consolidation of identical files, but
with different values in the cells. So I have to maintain the formulas and
sum the values.

The problem is that I created my own formula as the code bellow (onglet is
the sheetname) and Excel writes in the cells the formula itself, it does
not
calculate it as I expected. For exemple, it returns:

+Plan1!+Plan2! and not the value of the sum of the 2 cells.

What should I do???

Thanks in advance.

Daniel (Brazil)



Dim formula As Variant

Sheets("Input").Select

For a = 2 To 3
If Cells(a, 1).Value = True Then
Onglet = Cells(a, 3).Value
formula = formula & "+" & Onglet & "!" & RC

End If
Next a

Sheets("Conso").Select
Cells.Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.FormulaR1C1 = formula

End Sub

Writing own formula
 

Thanks everybody!

"Tom Ogilvy" wrote:

Dim rng as Range
On error resume next
set rng = Selection.SpecialCells(xlCellTypeConstants, 23)
On error goto 0
if not rng is nothing then
rng.select
' code for a successful selection
else


end if

--
Regards,
Tom Ogilvy


"dspilberg" wrote:

Nick,

thanks! It is working well.

Now I have another question.

Can I test the command

Selection.SpecialCells(xlCellTypeConstants, 23).Select

and only continue if this command returns a selection. Or use an On Error
Resume Next and tell VBA to go 3 lines lower in the code, which I don't know
how to do?

Thanks a lot in advance!

Daniel (Brazil)

"dspilberg" wrote:

Nick, I added the "Option Explicit", declared all the variables and now I
have the error in RC. And now? Any ideas?

Thanks!

Nick, how can I use the Option Explicit mode?

"NickHK" wrote:

It would seem that you are not using "Option Explicit" in all you modules.
Add that, then see the error concerning RC.

NickHK

"dspilberg" wrote in message
...
My objective is to create a file of consolidation of identical files, but
with different values in the cells. So I have to maintain the formulas and
sum the values.

The problem is that I created my own formula as the code bellow (onglet is
the sheetname) and Excel writes in the cells the formula itself, it does
not
calculate it as I expected. For exemple, it returns:

+Plan1!+Plan2! and not the value of the sum of the 2 cells.

What should I do???

Thanks in advance.

Daniel (Brazil)



Dim formula As Variant

Sheets("Input").Select

For a = 2 To 3
If Cells(a, 1).Value = True Then
Onglet = Cells(a, 3).Value
formula = formula & "+" & Onglet & "!" & RC

End If
Next a

Sheets("Conso").Select
Cells.Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.FormulaR1C1 = formula

End Sub

Writing own formula
 

Tom,

in fact when this line returns nothing

set rng = Selection.SpecialCells(xlCellTypeConstants, 23)

I have prblems with the line

if not rng is nothing then

Error 424.

Thanks!



"Tom Ogilvy" wrote:

Dim rng as Range
On error resume next
set rng = Selection.SpecialCells(xlCellTypeConstants, 23)
On error goto 0
if not rng is nothing then
rng.select
' code for a successful selection
else


end if

--
Regards,
Tom Ogilvy


"dspilberg" wrote:

Nick,

thanks! It is working well.

Now I have another question.

Can I test the command

Selection.SpecialCells(xlCellTypeConstants, 23).Select

and only continue if this command returns a selection. Or use an On Error
Resume Next and tell VBA to go 3 lines lower in the code, which I don't know
how to do?

Thanks a lot in advance!

Daniel (Brazil)

"dspilberg" wrote:

Nick, I added the "Option Explicit", declared all the variables and now I
have the error in RC. And now? Any ideas?

Thanks!

Nick, how can I use the Option Explicit mode?

"NickHK" wrote:

It would seem that you are not using "Option Explicit" in all you modules.
Add that, then see the error concerning RC.

NickHK

"dspilberg" wrote in message
...
My objective is to create a file of consolidation of identical files, but
with different values in the cells. So I have to maintain the formulas and
sum the values.

The problem is that I created my own formula as the code bellow (onglet is
the sheetname) and Excel writes in the cells the formula itself, it does
not
calculate it as I expected. For exemple, it returns:

+Plan1!+Plan2! and not the value of the sum of the 2 cells.

What should I do???

Thanks in advance.

Daniel (Brazil)



Dim formula As Variant

Sheets("Input").Select

For a = 2 To 3
If Cells(a, 1).Value = True Then
Onglet = Cells(a, 3).Value
formula = formula & "+" & Onglet & "!" & RC

End If
Next a

Sheets("Conso").Select
Cells.Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.FormulaR1C1 = formula

End Sub

Writing own formula
 

Did you surround that line with:

on error resume next
set rng = ...
on error goto 0

If yes, you may want to share your code.

dspilberg wrote:

Tom,

in fact when this line returns nothing

set rng = Selection.SpecialCells(xlCellTypeConstants, 23)

I have prblems with the line

if not rng is nothing then

Error 424.

Thanks!

"Tom Ogilvy" wrote:

Dim rng as Range
On error resume next
set rng = Selection.SpecialCells(xlCellTypeConstants, 23)
On error goto 0
if not rng is nothing then
rng.select
' code for a successful selection
else


end if

--
Regards,
Tom Ogilvy


"dspilberg" wrote:

Nick,

thanks! It is working well.

Now I have another question.

Can I test the command

Selection.SpecialCells(xlCellTypeConstants, 23).Select

and only continue if this command returns a selection. Or use an On Error
Resume Next and tell VBA to go 3 lines lower in the code, which I don't know
how to do?

Thanks a lot in advance!

Daniel (Brazil)

"dspilberg" wrote:

Nick, I added the "Option Explicit", declared all the variables and now I
have the error in RC. And now? Any ideas?

Thanks!

Nick, how can I use the Option Explicit mode?

"NickHK" wrote:

It would seem that you are not using "Option Explicit" in all you modules.
Add that, then see the error concerning RC.

NickHK

"dspilberg" wrote in message
...
My objective is to create a file of consolidation of identical files, but
with different values in the cells. So I have to maintain the formulas and
sum the values.

The problem is that I created my own formula as the code bellow (onglet is
the sheetname) and Excel writes in the cells the formula itself, it does
not
calculate it as I expected. For exemple, it returns:

+Plan1!+Plan2! and not the value of the sum of the 2 cells.

What should I do???

Thanks in advance.

Daniel (Brazil)



Dim formula As Variant

Sheets("Input").Select

For a = 2 To 3
If Cells(a, 1).Value = True Then
Onglet = Cells(a, 3).Value
formula = formula & "+" & Onglet & "!" & RC

End If
Next a

Sheets("Conso").Select
Cells.Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.FormulaR1C1 = formula

End Sub




--

Dave Peterson