Lookup function in VBA

[Laavista]

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.

 

[Domenic]

Try...

    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...

    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...

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]

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!!!