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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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
 
Upvote 0
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!!!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

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
Back
Top