Looking for VB macro...

[woodcock]

I am looking for some VB macro help. I have two worksheets in a
spreadsheet. On the first worksheet, in column A, starting on row 2, I
have an ID number (1655), on the next row the next ID number, so on and
so on.

Something like this:

1655
1634
1623
1676
1742
on and on ...

I would like a macro to use the first value in the column A2(1655), and
search for that value in the second worksheet and copy the data two
cells over to the right..

Example:


1655 10.1.1.2 /30 00:15:2g:94:bc:0f yes
1623 10.1.1.32 /30 00:15:2g:45:we:0t no
1745 10.1.1.23 /30 00:15:2e:3w:e3:07 yes


in the example there are 5 columns(A-E), Using the value from the first
sheet, 1655, I want the macro to search the worksheet for that value,
when it finds the row, copy the data two columns over, (column D) and
paste that data into another worksheet.

So in this example it would search the second worksheet for the value
1655, when the row was found it would copy the data in in column D
(00:15:2g:94:bc:0f ) and paste it in another worksheet.

Then I would like the search to start over and use the value from the
next row on the first worksheet A3(1634). And then move on to the next
value A4, so on and so on, until there is nothing left in column A on
the first sheet.
Thanks for any help on this..

Michael.

 

[jennifer1970]

Look in the help file for the VLOOKUP function. It should do what you
want.

 

[Don Guillett]

this might help?

Sub findandcopy()
For Each c In Sheets("sheet1") _
..Range("e1:e" & Cells(Rows.Count, "e").End(xlUp).Row)
Sheets("report").Columns(1).Find(c, lookat:=xlWhole).Offset(, 2).Copy _
Sheets("sheet1").Range("f1") 'you didn't say where??
Next
End Sub

 

[woodcock]

Hello,

Thanks for your help! The findandcopy code kinda does what I want, but
I need it to find each value in the column, this seems to only find and
copy the last value in the column that its searching. It seems to skip
everything in the column and just search and copy the last one. Is
there a way to find each value in the column, paste it? Again thanks
for all your help.


Michael

 

[Don Guillett]

As I said,
"you didn't say where"
what is happening, since you didn't say where, is that it is copying each to
the SAME cell so it appears that it only did the last one. Mind reading is
extra.

 

[woodcock]

Hello again!

Thanks Don for the info! How would I put the results starting where
you have it on the code, starting on F1 and the next result on F2 and
so and so on. How would I accomplish that? Again thanks for you help!

M

 

[Don Guillett]

try this. modify to suit

Sub findandcopy()
For Each c In Sheets("sheet1") _
..Range("e1:e" & Cells(Rows.Count, "e").End(xlUp).Row)
dlr= sheets("sheet1").cells(rows.count,"f").end(xlup).row+1
Sheets("report").Columns(1).Find(c, lookat:=xlWhole).Offset(, 2).Copy _
Sheets("sheet1").Range("f" & dlr) 'you didn't say where??
Next
End Sub

 

[woodcock]

Hello,

This works great, thanks for all your help. Just curious, how hard
would it be to have the data pasted in columns instead of rows?

thanks again for all your help on this!

m