CODEWITHSUNDEEP
excelvba
JeromeVBA
JeromeVBA

Reputation: 13

vba code to reference letter and number in excel formula

This is my piece of code:

For loopi = maxlastrow + 3 To lastrowrangetotal
issuerformula = Cells(loopi, 4).Address(rowabsolute:=False, columnabsolute:=False)
Worksheets("Dashboard").Cells(loopi, 4).Formula = "=BDP(""&issuerformula.value&"",""ISSUER"")"
Next loopi

this part assign a variable to a cell expressed in "letter / number", let's say D35 in this case.

This piece of code manage to transform a cell expressed as cell(2,2) for example into B2 : 

issuerformula = Cells(loopi, 4).Address(rowabsolute:=False, columnabsolute:=False)

I would like to use this variable to have in my cell the formula "=BDP(B2,"ISSUER")"

However, the way it is coded now returns that : =BDP("&issuerformula.value&","ISSUER")

I tried several combinations, removing ".value", doubling or tripling """,... no chance for me.

What would be the correct combination to do that in this formula Worksheets("Dashboard").Cells(loopi, 4).Formula = "=BDP(""&issuerformula.value&"",""ISSUER"")"

Upvotes: 0

Views: 72

Answers (1)

JeromeVBA
JeromeVBA

Reputation: 13

it's ok I found the answer myself, it was a matter of blank

so, this works...

For loopi = maxlastrow + 3 To lastrowrangetotal
      issuerformula = Cells(loopi, 4).Address(rowabsolute:=False, columnabsolute:=False)
      Worksheets("Dashboard").Cells(loopi, 4).Formula = "=BDP(" & issuerformula & ",""ISSUER"")"
Next loopi

Upvotes: 1

Related Questions