Correcting Debug '424 Error'

Fluff

MrExcel MVP, Moderator

Joined

Jun 12, 2014

Messages

90,412

Office Version

1. 365

Platform

1. Windows

• Feb 1, 2018

• #2

Hi & welcome to the board
Change Activate to Application

 

Last edited: Feb 1, 2018

Upvote 0

B

bsmith579

New Member

Joined

Feb 1, 2018

Messages

7

• Feb 1, 2018

• #3

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.

 

Upvote 0

Fluff

MrExcel MVP, Moderator

Joined

Jun 12, 2014

Messages

90,412

Office Version

1. 365

Platform

1. Windows

• Feb 1, 2018

• #4

try

Code:

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

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

 

Upvote 0

B

bsmith579

New Member

Joined

Feb 1, 2018

Messages

7

• Feb 1, 2018

• #5

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.

 

Upvote 0

Fluff

MrExcel MVP, Moderator

Joined

Jun 12, 2014

Messages

90,412

Office Version

1. 365

Platform

1. Windows

• Feb 1, 2018

• #6

Try

Code:

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

 

Upvote 0

B

bsmith579

New Member

Joined

Feb 1, 2018

Messages

7

• Feb 1, 2018

• #7

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

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Feb 1, 2018

• #8

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()

bsmith579 said:

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

Click to expand...

Should be

Code:

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

 

Last edited: Feb 1, 2018

Upvote 0

B

bsmith579

New Member

Joined

Feb 1, 2018

Messages

7

• Feb 1, 2018

• #9

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?

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Feb 1, 2018

• #10

Perhaps.

Code:

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

 

Upvote 0