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.

[mdmackillop]

Can you post a sample workbook?

[Laavista]

I'd be glad to submit a sample workbook, but am not sure how to upload it. Can you tell me to refer me to a link regarding how to do this?

[mdmackillop]

Use Manage Attachments in Go Advanced reply section

[Laavista]

(Sorry-- I found the 'attach' icon right after posting my question...)

Attached is a sample worksheet.

Thanks for your time!

[mdmackillop]

Either of these (both based on Selection)


Option Explicit

Sub DetermineWorkDay()
Dim cel As Range, c As Range
For Each cel In Selection
Set c = Sheets("Workday").Columns(1).Find(cel, LookIn:=xlFormulas)
If Not c Is Nothing Then
cel.Offset(, 2) = c.Offset(, 1)
End If
Next
End Sub

Sub DetermineWorkDay2()
Dim cel As Range, c
For Each cel In Selection
c = Application.VLookup(cel, Sheets("Workday").Range("A:B"), 2, False)
cel.Offset(, 1) = c
Next
End Sub

[Laavista]

Thank you so much for taking the time to help me with this. Works great!

REALLY appreciate your help!