Adding a formula in VB

[mandye]

I have a macro set up to populate the active cell with a vlookup formula based on cell entries offset to the left and above of the active cell...


Sub Lookup_Formula()

Dim c, d, e As String

c = ActiveCell.Offset(0, -4).Value
d = ActiveCell.Offset(0, -2).Value
e = ActiveCell.Offset(-2, 0).Value

ActiveCell.Formula = "=vlookup("" & c & ""," & d & "_overview," & e & ",false)"

End Sub


Unfortunately the macro returns it as

=VLOOKUP(" & c & ",Miscellaneous_Cross_Checks_overview,1,FALSE)

Can someone tell me how to add in the value of C with quotation marks, rather than how it is returning it as above.

Thank you in anticipation.

 

[Legacy 12630]

Try this, but not sure it's what you're looking for :

ActiveCell.Formula = "=vlookup(" & c & "," & d & "_overview," & e & ",false)"


Also, you are only declaring the variable "e" as String.

If you want to declare all variables (not that it really matters in this case), then :-

Dim c as String, d as String, e As String

Or :

Dim c$, d$, e$


Also, a more efficient code structure ;-

With ActiveCell

c = .Offset(0, -4).Value
d = .Offset(0, -2).Value
e = .Offset(-2, 0).Value

.Formula = "=vlookup(" & c & "," & d & "_overview," & e & ",false)"

End with

 

[Todd Bardoni]

ActiveCell.Formula = "=vlookup(c & "," & d & "_overview," & e & ",false)"

I think this is what you're looking for.

 

[mandye]

Thank you for your quick response, but I actually have to have C in quotation marks though, so that it reads

=vlookup("c",d, e, false)

Any other suggestions?

 

[Todd Bardoni]

Then, "=vlookup("c," & d & "_overview," & e & ",false)"

 

[Legacy 12630]

Is this it? :-

ActiveCell.Formula = "=vlookup(""c""," & d & "_overview," & e & ",false)"

 

[mandye]

Sorry for the confusion here but C is a variable and returns the value from another range, for example c = monday


I need the formula to return

vlookup("monday", d,e,false)

The suggestions that have been given are returning

vlookup("c",d,e,false) or vlookup(monday,d,e,false)

Basically I need the monday/c to appear in quotes.

Please keep persevering! :D

 

[Todd Bardoni]

Ok, I'll give it one more try:

"=vlookup("""& c & ""," & d & "_overview," & e & ",false)"

 

[Legacy 12630]

Try using the macro recorder to get the syntax.