wsnyder
Board Regular
- Joined
- Sep 23, 2018
- Messages
- 223
- Office Version
- 365
- Platform
- Windows
Hi all,
Using Excel 365.
In my code, I am building up a Vlookup formula
I'm getting an error:
I created the formula on the grid : =VLOOKUP("cat",A2:B4,2,FALSE)
And when I print out the formula built in code I get this : =VLOOKUP{"cat",$A$2:$B$4,2,FALSE)
Seems the same to me other than the absolute ref's
Any ideas?
Thanks,
-w
Using Excel 365.
In my code, I am building up a Vlookup formula
I'm getting an error:
In code below, I used a simple formula "=1+2" and that worked so it is something in my VLookup formula.Run-time error '1004':
Application-defined or object-defined error
I created the formula on the grid : =VLOOKUP("cat",A2:B4,2,FALSE)
And when I print out the formula built in code I get this : =VLOOKUP{"cat",$A$2:$B$4,2,FALSE)
Seems the same to me other than the absolute ref's
Any ideas?
Thanks,
-w
VBA Code:
Option Explicit
Sub foo()
'=VLOOKUP("cat",A2:B4,2,FALSE)
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim rngFrmla As Range
Dim colBegin As Long
Dim colEnd As Long
Dim rowEnd As Long
Dim i As Long 'Begin Col
Dim j As Long 'End Col
Dim strColBegin As String
Dim strColEnd As String
Dim LookupArray As String
Dim LookupFormula As String
Const EndColHeader As String = "Number"
Const BeginColHeader As String = "Animal"
Const LookupTerm As String = "cat"
Const rowBegin As Long = 2
Set wb = ThisWorkbook
Set ws = wb.Worksheets(1)
Set rng = ws.Rows("1:1")
rowEnd = GetRows(ws:=ws)
'Begin Column - number
i = FindColumnHeader(rng:=rng, _
SearchTerm:=BeginColHeader)
'End Column - number
j = FindColumnHeader(rng:=rng, _
SearchTerm:=EndColHeader)
'Begin Column - letter
strColBegin = Split(Cells(1, i).Address, "$")(1)
'End Column - letter
strColEnd = Split(Cells(1, j).Address, "$")(1)
'Lookup Array String
LookupArray = "$" & strColBegin & "$" & rowBegin & ":$" & strColEnd & "$" & rowEnd
'Lookup Formula - Exact match
'j = column to return
LookupFormula = "=VLOOKUP{" & """" & LookupTerm & """" & "," & LookupArray & "," & j & ",FALSE)"
Debug.Print LookupFormula
'Range for formula
With ws
Set rngFrmla = .Range(.Cells(2, 6), .Cells(rowEnd, 6))
End With
'Apply formula
rngFrmla.Formula = LookupFormula
'Tidy up
Set rng = Nothing
Set ws = Nothing
Set wb = Nothing
End Sub