vba to write formula with dynamic cell reference

Question

=BDP(F3& " cusip", "security des")

Hi all,

I am trying to write a macro that will insert the above exact formula (with the only difference being the F3 reference cell) into my current active cell. Below is the actual vba code.

I just need the "F3" cell below to reference a variable cell that is determined by Excel/vba every time that I run the macro.

I already have the code to have vba/Excel determine the cell. Lets just say this variable cell is set as CName. So CName houses the cell I would like to use--be it F3, D2 or whatever.

ActiveCell.Formula = "=BDP(F3&"" cusip"", ""security des"")"

Can someone help? Thank you!

Answer 1

You can make Cname variable dynamic if you like:

CName = Range("F3").Address
ActiveCell.Formula = "=BDP(" & CName & Chr(34) & " cusip" & Chr(34) & ", " & Chr(34) & "security des" & Chr(34) & ")"

Answer 2

If CName is the address of the cell you want to have the formula in, then try something like:

ActiveCell.Formula = "=BDP(" & CName & ""& cusip"", ""security des"")"

Answer 3

Thank you all for the feedback and support! I figured it out after taking a break and reviewing your ideas! So by inserting the below will do the trick! I guess .address (,) is absolutely necessary.

& CName.Address(False, False) &

Thanks again!