CODEWITHSUNDEEP
vbaformula
Growth128
Growth128

Reputation: 1

vba to write formula with dynamic cell reference

=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!

Upvotes: 0

Views: 3792

Answers (3)

Growth128
Growth128

Reputation: 1

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!

Upvotes: 0

Tehscript
Tehscript

Reputation: 2556

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) & ")"

Upvotes: 1

mooseman
mooseman

Reputation: 2015

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"")"

Upvotes: 0

Related Questions