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

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

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

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 -

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 -

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 -

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

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

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

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

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

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

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