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:
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,227
Office Version
  1. 365
Platform
  1. Windows
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!!!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,155
Messages
5,835,697
Members
430,379
Latest member
QCLAN

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