Vba to search and copy cells in row [Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hi,

i need to know how to copy certain cells in a row. I have the vba to search for a value in column A, but i need to know how to do the following....

e.g
Cell A24 contains the value im looking for, now i need to copy cells in that row (B24, D24, F24, G24).

I need this to happen for any value i search for in column A.

Hope this makes sense.

Thanks in advance for your help.

PM

Excel 2003

1 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
792
try this macro
all columns must have headings in row no. 1


Sub test()
Dim x As Double, r As Range, y(1 To 4) As Double, cfind As Range
Dim j As Integer
x = InputBox("type the number you want to find e;g. 21")
Set r = Range(Range("A2"), Range("A2").End(xlDown))
Set cfind = r.Cells.Find(what:=x, lookat:=xlWhole, LookIn:=xlValues)

y(1) = Cells(cfind.Row, "B")
y(2) = Cells(cfind.Row, "d")
y(3) = Cells(cfind.Row, "f")
y(4) = Cells(cfind.Row, "g")

For j = 1 To 4
Worksheets("sheet2").Range("A1").Offset(0, j - 1) = y(j)
Next j

End Sub

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!