MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Find a value in a column, paste a row of data


Posted by Ian Bartlett on April 14, 2001 11:23 PM

I have a column containing sequential dates, and I'd like to find a particular date in that column and paste a row of data beside it, starting one column to the right. I have the code for pasting the data, can anyone help with code for finding the date in the column and running the paste code?

Many thanks,

Ian


Posted by steve on April 15, 2001 8:21 AM


Hi Ian,
how about trying vlookup,

=VLOOKUP($D4,data_area,2)

I named "data area" as a named range(you can enter it as a normal range if you want), "$D4" is the number you want to look up and the number 2 is what column over of the data you want.Your date will have to be in the first column on the left in the data_area range.

Hope this helps
steve

Posted by Ian Bartlett on April 15, 2001 11:47 AM


Hi Steve,

Thanks for the reply. I can pull data out once it's in using vlookup, but my problem is with getting the data in in the first place. I think that I need something like a for-next loop that looks at the first date in the column. If it equals the date of the new row to paste, it runs the 'paste' code. If not, it moves down to the next row and repeats the test etc until a match is found.

I know the basic syntax for for-next and if-then else; it's the comparing the present cell with my new data cell value that I'm stuck on. Can you help with this?

Thanks again,

Ian

Posted by Malc on April 15, 2001 2:45 PM


Sub GetDate
'Use a input box to get the date or date from a cell. Output = SelectedDate
End Sub

Sub CopyData
Range(cells(1,6),cells(1,10)).select'Selected Data Range for e.g.
Selection.Copy
End Sub

Sub SeeIfDateMatches
X=1
do
X=X+1
if Cells(X,1) = SelectedDate then 'Assume date col is colA
PASTEDATA
Else
MsgBox"No Matching Dates"
End IF
Loop Until Cells(X+1,1)=""
end Sub

Sub PASTEDATA
Cells(X,2).select
ActiveSheet.Paste
End Sub

I don't have excel at home so can't test it but it might work You'll have to put a loop over the whole thing if there's more than one lot of dates to find in the col. And you'll need to maintain variables between modules i.e use the Private function

Posted by Ian Bartlett on April 16, 2001 11:49 PM

Malc,

Thanks for that - you gave me the bits of code that I was missing, problem solved.

Ian