Previous Topic Next Topic

Vba to search and copy cells in row

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 -
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

Answer 1 / 1
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 803
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
Ask a question