-
lookup function in VBA
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.
-
Can you post a sample workbook?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
how submit sample workbook
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?
-
Use Manage Attachments in Go Advanced reply section
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
sample of worksheet for question re:lookup
(Sorry-- I found the 'attach' icon right after posting my question...)
Attached is a sample worksheet.
Thanks for your time!
-
Either of these (both based on Selection)
[VBA]
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
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
SOLVED
Thank you so much for taking the time to help me with this. Works great!
REALLY appreciate your help!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules