VLookup in VBA Error 424: Object required

[ctouchberry]

Hello,

I'm debugging this as I go, and I'm getting a 424 error on the VLookup line.

I'm trying to load values from "04-Space" in the 2nd column into "JCX" in the 8th column, but only after checking to see if the 04-Space value is in the 1st column in 03-Floor.

The reason for the variables is that while the current data source only has a "Lower Level", other projects might have more than 1 floor, and I also need to account for differences such as "First Floor" and "1st Floor" in the variable.

This is the variable assignment code

Dim FloorName As String
FloorName = "Lower Level"

This is the other code

TagRowCounter = 2
Do
    Sheets("04-Space").Cells(TagRowCounter, 2).Value = FloorName
    
    Application.WorksheetFunction.VLookup(FloorName, (Sheets("03-Floor").Cells(TagRowCounter, 1)), 1, False).Value
    
    Sheets("JCX").Cells(TagRowCounter, 8).Value = Sheets("04-Space").Cells(TagRowCounter, 2).Value
    TagRowCounter = TagRowCounter + 1
    
Loop Until Sheets("04-Space").Cells(TagRowCounter, 1) = False

Any thoughts? Thanks in advance!

 

[StephenCrump]

The immediate problem is the unnecessary .Value on the end of the VLookup, and the fact that you haven't assigned the value of the VLookup to a variable.

I don't understand what your code is trying to do ... writing the same FloorName to successive rows in "04-Space" and then assigning the same value to successive rows in "JCX"?

But here's one way to do what I think you might be trying to do:

On Error Resume Next
MatchRow = Application.Match(FloorName, Sheets("03-Floor").Range("A:A"), 0)
On Error GoTo 0
If MatchRow <> 0 Then
    'We have a match in column A!
    'what do you want to do here?
End If

 

[ctouchberry]

I've updated my code as follows. I'm trying to take a cell from 07-Component, find it on 04-Space, and then load the value in the column to the immediate right into the H/8 column in JCX. This then loops.

I'm looking at rooms in 07-Component, finding the applicable floor in 04-Space, and then loading the floor value into the Floor column in JCX. Does that help?

In 04-Space, Rooms are in Column A, Floors are in Column B.

'Floor
'Row Column
TagRowCounter = 2
Do
    Sheets("JCX").Cells(TagRowCounter, 8).Value = Application.WorksheetFunction.VLookup(Sheets("07-Component").Cells(TagRowCounter, 2), Sheets("04-Space").Range(A, B), 2, 0)
    TagRowCounter = TagRowCounter + 1
    
Loop Until Sheets("04-Space").Cells(TagRowCounter, 1) = False

 

[ctouchberry]

Also, that code is giving me a "Run time error 1004: Application-defined or object-defined error" message. Sorry for not including that in my previous post.

 

[StephenCrump]

Change: Range(A,B) to Range("A:B")

Is your loop boundary correct? On the face of it, it would appear to make more sense to loop until the end of Sheets("07-Component")?

You could also do it without looping:

LastRow = 99    'Determine this dynamically, e.g. LastRow = Sheets("07-Component").Range("B" & Rows.Count).End(xlUp).Row

With Sheets("JCX").Range("H2:H" & LastRow)
    .Formula = "=IFERROR(VLOOKUP('07-Component'!B2, '04-Space'!A:B, 2,),""Can't find!"")"
    .Value = .Value
End With

 

[ctouchberry]

Ended up doing this. Only took another glance at the syntax :P

I forgot the quotes around the values in the Range section. Works like a champ.

Do
    Sheets("JCX").Cells(TagRowCounter, 8).Value = Application.VLookup(Sheets("07-Component").Cells(TagRowCounter, 2), Sheets("04-Space").Range("A:B"), 2, 0)
    TagRowCounter = TagRowCounter + 1
    
Loop Until Sheets("07-Component").Cells(TagRowCounter, 2) = False