Solved: Cell reference in VB

[frubeng]

Hi,

I have a macro that runs the line (among others):
Workbooks("beta.xlsm").Worksheets("beta").Cells(x + counter2, 4) = "=BDP(RC[-3] & "" Equity"",""EQY_BETA_RAW_OVERRIDABLE"",""EQY_BETA_OVERRIDE_REL_INDEX"",Results!F1)"

However, the cell content shows up as:

=BDP(A14 & " Equity","EQY_BETA_RAW_OVERRIDABLE","EQY_BETA_OVERRIDE_REL_INDEX",Results!'F1')
In the excel cell. The problem is that the "F1" is surrounded by apostrophes which is causing my BDP function not to work . Anyone have any ideas?

Thanks!

[shrivallabha]

Something like:
Check = Mid(Range("I6").Value, 2, ((Len(Range("I6").Value) - 2)))
Hope this helps,

[Bob Phillips]

That is because you are mixin A1 notation in with R1C1 notation. Use R1C6 instead of F1.

[mikerickson]

The formula in the OP mixes R1C1 notation with A1 notation. Try
Dim f1String as String

With ThisWorkbook.Worksheets("beta").Cells(x + counter2, 4)

f1String = Worksheets("Results").Range("f1").Address(False, False, xlR1C1, True, .Cells)

.FormulaR1C1 = "=BDP(RC[-3] & "" Equity"",""EQY_BETA_RAW_OVERRIDABLE"",""EQY_BETA_OVERRIDE_REL_INDEX""," & f1String & ")"
End With

[frubeng]

Thanks for the help, did not know mixing would cause an issue!

[mdmackillop]

Hi Frubeng
If this is solved, please mark it so using the Thread Tools dropdown.
Regards
MD