LookUp Code in VBA

**pezalmendra** · 08-30-2012, 04:00 AM

Hi,

Im using the following lookup code in VBA but cannot make it work:

Range("AU" & x) = Sheet2.Application.WorksheetFunction.Lookup(Range("A" & x).Value, Range("A9:A15"), Range("F9:F15").Value)

The thing is that:

Range("A" & x) is located in Sheet3

Range("A9:A15") is located in Sheet2

Range("F9:F15") is located in Sheet2

Thanks!

Regards,

**zbor** · 08-30-2012, 04:10 AM

Try

Sheets("Sheet2").Range("AU" & x).value = Application.WorksheetFunction.Lookup(Sheets("Sheet3").Range("A" & x), Sheets("Sheet2").Range("A9:A15"), Sheets("Sheet2").Range("F9:F15"))

**pezalmendra** · 08-30-2012, 05:23 AM

Hi!

It pops up an error also... "Unable to get the Lookup property of the WorksheetFunction class"

**pezalmendra** · 08-30-2012, 07:37 AM

Hi again!

I manage to make work it work with the code above but it displays #NA instead the values... strange...

I have attached my workbook if it helps.

Thanks!!

Regards

**zbor** · 08-30-2012, 08:57 AM

Originally Posted by pezalmendra

I manage to make work it work with the code above but it displays #NA instead the values... strange...

Because your first value is range and not cell.

Sheets("Trips Overview").Range("AU9:AU20") = Application.WorksheetFunction.LookUp(Sheets("Trips Overview").Range("A9:A20").Value, Sheets("Schedule Stops").Range("A9:A15"), Sheets("Schedule Stops").Range("X9:X15").Value)

Check mine example above. You need to use first cell Sheets("Trips Overview").Range("AU9") and then loop to others.
see mike's solution.

**mike7952** · 08-30-2012, 09:33 AM

Also if your going to use the Lookup you need to convert your values in column A to numbers

For i = 9 To 15
    Sheets("Trips Overview").Range("AU" & i) = _
        Application.WorksheetFunction.LookUp(Sheets("Trips Overview").Range("A" & i).Value, _
        Sheets("Schedule Stops").Range("A9:A15"), _
        Sheets("Schedule Stops").Range("X9:X15").Value)
Next

**mike7952** · 08-30-2012, 08:43 AM

Give this a try,

Sub LookUp()
Const shStops As String = "Schedule Stops"
Const shTrips As String = "Trips Overview"
Dim wsTrips As Worksheet, wsStops As Worksheet
Dim FoundCell As Range
Dim Ptr As Long, LastRow As Long
    
    Set wsTrips = Worksheets(shTrips)
    Set wsStops = Worksheets(shStops)
    
    LastRow = wsTrips.Cells(Rows.Count, "a").End(xlUp).Row
    With wsStops
        For Ptr = 9 To LastRow
            Set FoundCell = .Range("a9:a" & .Cells(Rows.Count, "a").End(xlUp).Row) _
            .Find(What:=wsTrips.Cells(Ptr, "a"), Lookat:=xlWhole)
            
            If Not FoundCell Is Nothing Then
                wsTrips.Cells(Ptr, "au").NumberFormat = "[h]:mm"
                wsTrips.Cells(Ptr, "au") = .Cells(FoundCell.Row, "x")
            End If
        Next
    End With
    Set wsTrips = Nothing
    Set wsStops = Nothing
    Set FoundCell = Nothing
End Sub

**pezalmendra** · 08-30-2012, 10:43 AM

Yesss!! works great mike!!!
thanks you both for the help and attention!!!

Regardssss!!!

**Cutter** · 08-30-2012, 01:13 PM

@ pezalmendra

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

LookUp Code in VBA

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

LookUp Code in VBA

Re: LookUp Code in VBA

Re: LookUp Code in VBA

Re: LookUp Code in VBA

Re: LookUp Code in VBA

Re: LookUp Code in VBA

Re: LookUp Code in VBA

Re: LookUp Code in VBA

Re: LookUp Code in VBA

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions