Range Formula

wsnyder

Board Regular
Joined
Sep 23, 2018
Messages
223
Office Version
  1. 365
Platform
  1. Windows
Hi all,

Using Excel 365.
In my code, I am building up a Vlookup formula
I'm getting an error:
Run-time error '1004':
Application-defined or object-defined error
In code below, I used a simple formula "=1+2" and that worked so it is something in my VLookup formula.

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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
You have a "{" instead of "(" in building your VLOOKUP formula.
Rich (BB code):
    LookupFormula = "=VLOOKUP{" & """" & LookupTerm & """" & "," & LookupArray & "," & j & ",FALSE)"
 
Upvote 0
Solution
Thanks Joe4,

They need to make the difference more pronounced somehow - or maybe I need glasses.
I've been looking at that for 1/2 hour trying to figure out "What the heck?"

Thanks again,
-w
 
Upvote 0
You are welcome!

We have all been there! Sometimes, it just takes a fresh set of eyes.
Your brain starts tricking you into seeing what you expect to be there, instead of what is really there.

I cannot tell you how many times that happens to me when writing code!
I find that walking away from it for a few hours and coming back to it often helps me spot my typos/obvious errors.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top