|
|
|
|||||||
| Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. |
 |
|
|
Thread Tools | Display Modes |
May 21st, 2002, 01:48 AM
|
#1 |
|
Board Regular
Join Date: Mar 2002
Posts: 160
|
Hiya all, I'm looking to do the following. I have a worksheet that looks like:
Name TodayDate Value ABC 21/05/02 102 GUY 21/05/02 121 DIR 21/05/02 878 I have another worksheet in that workbook that has ABC, GUY, DIR in row 3, so: Date DIR ABC GUY 18/05/02 154 545 454 19/05/02 455 124 432 20/05/02 178 566 335 21/05/02 I want some code whereby it will go through the initial list and find the match for column name (eg: ABC = ABC) and then find today's date (i.e. in the Date column of the second worksheet) and paste the data. So, in the second worksheet for today's date, you would see: Date DIR ABC GUY 18/05/02 154 545 454 19/05/02 455 124 432 20/05/02 178 566 335 21/05/02 878 102 121 I don't think I can use VLOOKUP as the initial worksheet is updated everyday with new info and the second worksheet retains it's data. Thks u...Benjy |
|
|
|
May 21st, 2002, 04:05 AM
|
#2 |
|
Board Regular
Join Date: Mar 2002
Posts: 160
|
This is what I have so far:
Sub bobLook() Dim todaysDate As Long Dim Keep_This As String ee = 1 ss = 2 ff = 1 todaysDate = CLng(Int(Now())) Keep_This = Worksheets("Ben").Cells(ee, 1).Value While (Worksheets("Ben").Cells(ee, 1).Value <> "") While (Worksheets("Day").Cells(3, ss).Value <> "") If (Worksheets("Day").Cells(3, ss).Value = Keep_This) Then While (Worksheets("Day").Cells(ff, 1).Value <> "") If CLng(Worksheets("Day").Cells(ff, 1).Value = todaysDate) Then 'Having trouble here ee = ee + 1 Keep_This = Worksheets("Ben").Cells(ee, 1).Value cc = 2 Else cc = cc + 1 End If Wend Wend End Sub |
|
|
|
|
May 21st, 2002, 04:25 AM
|
#3 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
MrExcel MVP
Join Date: Feb 2002
Location: Newcastle, UK
Posts: 1,174
|
Quote:
if you have a sheet2 with following info:
To see the formula in the cells just click on the cells hyperlink The above image was automatically generated by [HtmlMaker V1.27] If you want FREE SOFT, click here and Colo will email the file to you This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo Goto Insert>Names>Define Type NameField in the first box in the Refers To: type: =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1) click Add do the same for DateField and ValueField changing the Sheet2!$A$1 to Sheet2!$B$1/Sheet2!$C$1 respectively. now on sheet1 use: To see the formula in the cells just click on the cells hyperlink The above image was automatically generated by [HtmlMaker V1.27] If you want FREE SOFT, click here and Colo will email the file to you This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo Click the Underlined Numbers for the formulas. Hope this helps _________________ Share the wealth!! Ian Mac [ This Message was edited by: Ian Mac on 2002-05-21 03:26 ] |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
