Lookup function in VBA

Laavista

Board Regular
Joined
Aug 27, 2009
Messages
79
I have two worksheets.

====
Worksheet 2 is named WorkDay.
Column A contains a workday date (excluding holidays & weekends)
Column B contains a workday 'number' for that day

Example:
Col A Col B
6-30-2010 14895
7-1-2010 14896
7-2-2010 14897
7-6-2010 14898

=====
Worksheet 1 has dates in column C.

I need to lookup the date in column C and have the workday "number" stored in column D, e.g., for 7-1-10, the # 14896 should be in column D.

=====
I got it working using a function within the cell using:
=lookup(c6,Workday!$A$2:$A$3000,Workday!$B$2:$B$3000)

I need to use that same function in VBA. I tried

Dim TheWorkDate as long
Dim RowCount as long

Rowcount = 6

Range("D" & RowCount).Select

TheWorkDay = lookup((Rowcount & "c"),WorkDay$A$2:$A$3000,Workday!$B$2:$B$3000)

(I was then going to put the result in TheWorkDay in "D" & Rowcount.)

I get an error on the $ in the formula. If I take the $s out, I get "expected: list separate or ).

Your help would be very appreciated.
:confused:
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Try...

Code:
    Dim TheWorkDay As Variant
    Dim RowCount As Long
    
    RowCount = 6
    
    TheWorkDay = Application.Lookup(Range("C" & RowCount).Value, _
                    Worksheets("WorkDay").Range("A2:B3000"), _
                    Worksheets("WorkDay").Range("B2:B3000"))
        
    Range("D" & RowCount) = IIf(IsError(TheWorkDay), "N/A", TheWorkDay)

If you're actually looking for an exact match, use VLOOKUP instead of LOOKUP...

Code:
    TheWorkDay = Application.VLookup(Range("C" & RowCount).Value, _
                    Worksheets("WorkDay").Range("A2:B3000"), 2, 0)

If you'd like to loop through each cell in Column C, starting in Row 6, try...

Code:
Option Explicit

Sub test()

    Dim TheWorkDay As Variant
    Dim wksSheet1 As Worksheet
    Dim wksWorkDay As Worksheet
    Dim LastRow As Long
    Dim i As Long
    
    Set wksSheet1 = Worksheets("Sheet1")
    Set wksWorkDay = Worksheets("WorkDay")
    
    LastRow = wksSheet1.Cells(wksSheet1.Rows.Count, "C").End(xlUp).Row
    
    For i = 6 To LastRow
        
        TheWorkDay = Application.VLookup(wksSheet1.Range("C" & i).Value, _
                        wksWorkDay.Range("A2:B3000"), 2, 0)
                        
        wksSheet1.Range("D" & i) = IIf(IsError(TheWorkDay), "N/A", TheWorkDay)
        
    Next i

End Sub
 

Laavista

Board Regular
Joined
Aug 27, 2009
Messages
79
SOLVED: Lookup function in VBA

You're great! THANK YOU so much for taking the time to provide this information.

I really appreciate your help!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,180
Messages
5,594,713
Members
413,924
Latest member
OMGSALESFORCE2

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
Top