using VBA to put formulas in cells. excel is adding an @ in.

S

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
Hi i'm trying to use some VBA to update formulas.
I have a sheet that is constantly adding/deleting rows and columns.
So i want to update the formulas in my header section appropriately.

my code snipet.
.Range(Cells(2, 11), Cells(2, lastCol)).FormulaR1C1 = "=SUM(IFERROR(MID(R[19]C:R[" & lastRow - 2 & "]C,1,(FIND(""F"",UPPER(R[19]C:R[" & lastRow - 2 & "]C))-1))+0,0))"

the formula that ends up in the cell looks like:
=SUM(IFERROR(MID(@K21:K69,1,(FIND("F",UPPER(@K21:K69))-1))+0,0))

if you manually take out the @ the formula works.
but why is excel adding the @ as part of the cell reference?

Simi
 
adding this after the formula is set seems to work.

Code: 
'do a replace for @ to "" nothing.
         .Range(Cells(2, 11), Cells(2, lastCol)).Select
            Selection.Replace What:="@", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
 
You must log in or register to reply here.
Back
Top