Correcting Debug '424 Error'

[bsmith579]

I created the following code below and attached it to a button allowing me to enter a number into Cell A3, click my button and it displays the needed information from another worksheet. Overall it works, but I continue to get a "Run-Time error'424'" msg. What am I missing?

Sub ShowDispo()

Dim Res As Variant
Dim Hold As Range
Set Hold = Range("A3")
Set InitialRange = Sheets("Hold").Range("H:J")
Res = Activate.Vlookup(Hold, InitialRange, 3, True).Value

End Sub

 

[Fluff]

Hi & welcome to the board
Change Activate to Application

 

[bsmith579]

Thanks Fluff. I was hoping that was all there was to it but I still ran into the debug error ("Run-Time error '424': Object Required")

I also discovered that the cell that was displaying the data had the vlookup formula (=VLOOKUP(A3,'945 HOLDS'!H:J,3,FALSE) which is why I was seeing the data. I thought it was showing me because of the vba.
So I have taken 2 steps backwards: I have a debug problem and a display problem.

Here is my current correction:
Res = Application.VLookup(Hold, InitialRange, 3, True).Value = ("B2:O4") ----> B2:O4 is a merged cell. Not sure if this is the correct was to designate where I want the data to be seen.

 

[Fluff]

try

Res = Application.VLookup(Hold, InitialRange, 3, True).Value
Range("B2").Value = Res

But be warned, merged cells cause many problems & are best avoided.

 

[bsmith579]

Still running into same error.

Any other options of how/where to display results other than merged cells? The content of the information is pretty big which is why I had the cells merged in the first place.

 

[Fluff]

Try

Sub ShowDispo()

Dim Res As Variant
Dim Hold As String, initialrange As Range
Hold = Range("A3")
Set initialrange = Sheets("PostCodes").Range("H:J")
Res = WorksheetFunction.VLookup(Hold, initialrange, 3, False)
Range("B2").Value = Res

End Sub

 

[bsmith579]

I took it into a different direction after a little more research. The results showup in a msgBox instead of the merged cell (which I would still prefer, but could possibly work with this as well).

Sub Test1()Dim Result As Variant
Dim myVal As String
Dim Rng As Range
Dim Clm As Integer
Set Rng = Sheets("945 Holds").Range("H:J")
myVal = Sheets("Aircraft").Range("A3")
Clm = 3
Result = Application.VLookup(myVal, Rng, Clm, False)
If IsError(Result) Then
Result = "Not Found"
End If
MsgBox Result
End Sub

 

[Jonmo1]

The original code had 2 problems.
Application instead of Activate as Fluff pointed out.
But also, you can't put .Value on the end of Application.Vlookup()

Res = Activate.Vlookup(Hold, InitialRange, 3, True).Value

Should be

 Res = [COLOR=#ff0000]Application.[/COLOR]Vlookup(Hold, InitialRange, 3, True)

 

[bsmith579]

Thanks Jonmo1.

I made the correction you mentioned but it still does not show me a result. Don't I have to designate where I want to see the result?

 

[Norie]

Perhaps.

Sub ShowDispo()
Dim Res As Variant
Dim Hold As Range
    
    Set Hold = Range("A3")
    Set InitialRange = Sheets("Hold").Range("H:J")
    Res = Application.Vlookup(Hold, InitialRange, 3, True)

    If Not IsError(Res) Then
        Hold.Value = Res
    End If

End Sub