Previous Topic Next Topic

Vba to search and copy cells in row

Closed
PM - Apr 27, 2010 at 03:45 AM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Apr 27, 2010 at 06:21 AM
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
Related:

1 reply

Answer 1 / 1
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Apr 27, 2010 at 06:21 AM
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
0