Writing own formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
Hi
I don't know what your selection is but try

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

regards
Paul
 
Hi
What does the 23 refer to in your SpecialCells? The value normally
refers to text, numbers, true/false or errors.
regards
Paul
 
It would seem that you are not using "Option Explicit" in all you modules.
Add that, then see the error concerning RC.

NickHK
 
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
 
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!
 
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?
 
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)
 
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
 
Thanks everybody!

Tom Ogilvy said:
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
Click to expand...
 
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!
 
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.
 
Back
Top