Writing own formula

[dspilberg]

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

 

[paul.robinson]

Hi
Try
Selection.FormulaR1C1 = "=" & formula

regards
Paul

 

[dspilberg]

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!

 

[paul.robinson]

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

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

regards
Paul

 

[dspilberg]

Same error.

Thanks anyway!

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

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

regards
Paul

 

[paul.robinson]

Hi
What does the 23 refer to in your SpecialCells? The value normally
refers to text, numbers, true/false or errors.
regards
Paul

 

[NickHK]

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

NickHK

 

[paul.robinson]

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

 

[dspilberg]

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!

 

[dspilberg]

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?

 

[Dave Peterson]

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

 

[dspilberg]

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)

 

[Tom Ogilvy]

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

 

[dspilberg]

Thanks everybody!

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

 

[dspilberg]

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!

 

[Dave Peterson]

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.