Discussion Locked

XLVBA: find date in table

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a spreadsheet with two worksheets:

worksheet 1 has a cell with a date (say, 11/05/01)
worksheet 2 has a large table which has dates in col A.

Using VBA, can someone tell me how I can find the date in the large table using the date in worksheet 1 as the variable.

Please note, I am new to VBA and will need simple explaination with examples preferably.

Thanks very much.

5 replies

by peyison

What about using a formula instead? You haven't really said what you want to do when you look up the date, but if you want to get the value from another cell on that row, you can use the VLookup function.

Your formula would look something like:
=VLOOKUP(A1,Sheet2!A1:B7,2)

Where:

"A1" would contain the date you're looking for

"Sheet2!A1:E700" would be the range that contains your table

"2" would be the column that that you want to return.

In this case, it would return the value in the 2nd column of the table on Sheet2 on the row that matched the date contained in cell A1.

If this isn't what you're looking for, let me know and I can help with a VBA solution.

by atulkpatel

Thanks very much for the suggestions, Peyison . However, I would like a VBA solution as:

1. the date to be found in the worksheet2 will then be used to paste information to the right of that date found from say other information in worksheet1.
So, to make it more clear, here is what I need to do:

1. worksheet1 has a date
2. worksheet2 has a table, say A1:J200.
3. worksheet1 also has other data (which I failed to mentioned in previous comments).

a. using the date field in worksheet1, I need to find that date in the table in worksheet2. Once found, I need to copy and paste the other data in worksheet1 to the right of the found data field in worksheet2.

I apologise for not been clear the first time. I hope this is more helpful to you.

Thanks.

by sankulpune

Hello akrp10,

I assume that you have a .xls file that contains two worksheets.
The worksheet 'Source' contains a cell that has a date value.
You need to locate the cell in the other worksheet ('Destination') that contains the same date.

To do this, select the cell that contains the date to be serched and run the following VBA code:

'--------
Sub FindInDest()
Dim valueToFind, foundAt, sourceSheetName

' locates the cell in the other worksheet
' that contains the same value as inthe
' selected cell.
'
' Code by SanganakSakha, 4/8/01
' No comprehensive error checking done

valueToFind = Selection
sourceSheetName = ActiveSheet.Name

ThisWorkbook.Sheets("destination").Select
ActiveSheet.Cells.Select

foundAt = Selection.Find(What:=valueToFind, _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Address

ThisWorkbook.Sheets(sourceSheetName).Select

MsgBox foundAt

End Sub
'------

To be run this code,

1) Copy this code to clipboard.
2) Open the excel file.
3) Open Visual Basic Editor.
4) Paste the code in the code window for the sheet 'Source'.
5) Close the VBE.
6) Select the cell that contains the required date.
7) Tools > Macro > Macros...
8) select 'FindInDest' and click run.

The message box will show the address of the cell that contains the same value (any type, not restricted to date).The variable foundAt also contains thios value which you can use in your code.

I have successfully tried this solution and would be glad to provide any further help you may need to implement this solution.

With best compliments,

SanganakSakha,
Simple And Effective Solutions To Small Problems, No QuickFixes.

This discussion is closed to new replies.