0

I am trying to write a VLOOKUP in a cell as a string, with VBA. This means that I do not want the result to appear in the cell as a value, but I want the whole VLOOKUP expression instead (For this example : "VLOOKUP(C6,'[path_to_file.xlsm]OTD Table!$B:$F,4,0)"). The challenge is that the range argument of the VLOOKUP is a concatenation of a path (path_to_file.xlsm) that the user selects with a GetOpenFilename, and a string that specifies the tab in which the lookup table is located ("OTD Table!$B:$F,4,0"). The issue I am getting is very interesting :

When I print my expression in a Msgbox, the expression appears correctly. However, when I write it in a cell, the path mysteriously appears incorrectly. 

Sub macro()

dim data_file_new as String

data_file_new = CStr(Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xls*),*.xls*", Title:="Select new data file")) ' The user selects the file
str_ = "=VLOOKUP(C6," & "'[" & data_file_new & "]OTD Table!$B:$F,4,0)" ' This will display the expression correctly

cells(1,10)="=VLOOKUP(C6," & "'[" & data_file_new & "]OTD Table!$B:$F,4,0)"' This will not display the same thing as in the messagebox above

end Sub

I hope one of you guys can make sens of this !

izzymo
  • 916
  • 1
  • 10
  • 14
Vsamfh
  • 21
  • 3

2 Answers2

1

Because you're dropping a formula into a cell that you want to display as straight text, you have to be explicit with Excel and tag the text string to prevent interpreting it as a formula. The simplest way to do this is pre-pend the string with a single-quote "'".

Sub macro()
    Dim data_file_new, str_ As String

    str_ = "'=VLOOKUP(C6,'["
    data_file_new = CStr(Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xls*),*.xls*", Title:="Select new data file")) ' The user selects the file
    str_ = str_ & data_file_new & "]OTD Table!$B:$F,4,0)" ' This will display the expression correctly

    ActiveSheet.Cells(1, 10).Value = str_
End Sub
PeterT
  • 8,232
  • 1
  • 17
  • 38
0

Yeah either you'll need to set the string to add a single quote, or you'll need to change the numberformat of the cell to text (Cells(1,10).NumberFormat = "@")

Either of those should work.

shagans
  • 232
  • 3
  • 12