rrhandle8
asked on
Excel VBA inserting a formula
This is the formula I want to insert on the worksheet: f = "=IF(RC2=""B"",(RC6/RC4)-1 ,(RC4/RC6) -1)"
I print it to the Immediate window, and it is perfect.
What I get on the worksheet is all wrong: =IF(R[-5]C[464]="B",(R[-1] C[464]/R[- 3]C[464])- 1,(R[-3]C[ 464]/R[-1] C[464])-1)
I am stumped.
I print it to the Immediate window, and it is perfect.
What I get on the worksheet is all wrong: =IF(R[-5]C[464]="B",(R[-1]
I am stumped.
ASKER
What $ sign?
Please post your code, I will make changes
ASKER
f = "=IF(RC2=""B"",(RC6/RC4)-1 ,(RC4/RC6) -1)": Cells(i, 7) = f
Where do you want this formula on worksheet?
ASKER
COLUMN 7
And row?
ASKER
It has to go in about 20 rows.
Add below formula in G2 and drag until you want:
=IF(OR($B2="",$D2="",$F2=""),"",IF($B2="B",($F2/$D2)-1,($D2/$F2)-1))
If you are using code to try and put this formula in a sheet please post the whole code and/or upload a sample worksheet.
ASKER
I put "VBA" in the title.
Dim f As String
Sheets("WorkArea").Activate
For i = 4 To 8
f = "=BDP(RC[-4]&"" Equity"",""MARKET_PRICE"")": Cells(i, 5) = f
f = "=BDP(RC[-5]&"" Equity"",""LAST_PRICE"")": Cells(i, 6) = f
'f = "=IF(R[" & i & "]C2=""B"",(RC6/RC4)-1,(RC4/RC6)-1)": Cells(i, 7) = f '<-- I found this works
'f = "=IF(RC2=""B"",(RC6/RC4)-1,(RC4/RC6)-1)": Cells(i, 7) = f ' <-- this does not work, but should
f = "=RC[-1]*RC[-3]": Cells(i, 8) = f
f = "=RC[-1]": Cells(i, 9) = f
'''''''''''''''''''''''''''
' Only on change of "Index"
'''''''''''''''''''''''''''
f = "=SUMIF(C[-1],RC[-1],C[-6])": Cells(i, 11) = f
f = "=SUMIF(C[-1],RC[-1],C[-3])": Cells(i, 12) = f
f = "=RC[-1]/RC[-2]": Cells(i, 13) = f
'''''''''''''''''''''''''''
f = "=BDP(RC1&"" Equity"",""DVD_EX_DT"")": Cells(i, 15) = f
f = "=BDP(RC1&"" Equity"",""EQY_DVD_YLD_IND"")": Cells(i, 16) = f
Next
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
When I removed $ signs on worksheet it should look as below:
"=IF(RC[-5]=""B"",(RC[-1]/