Loop Formula

[Guest]

I have a range of cells lets say A1:B10. In Column B, there is an "x" every
so often. I need a macro that will look down Column B1:B10 and for every
time there is an "x" copy what is in the adjacent cell in Column A to Sheet1
cell A1 then A2 then A3 etc.

Please helpt.

 

[Guest]

Sub CopyData()
Dim rng as Range, cell as Range
dim rw as Long
set rng = Worksheets("Sheet2").Range("B1:B10")
rw = 1
for each cell in rng
if lcase(cell.value) = "x" then
worksheets("Sheet1").Cells(rw,"A") = cell.offset(0,-1)
rw = rw + 1
end if
Next
End sub