Copy one column's figures to another + if condition

[baron33]

My case is like this: Copy column B's number (if column A's adjecent cell is n/a) to Column A of sheet 2 from A1 to A2 to A3, etc (one by one)

Column A ColumnB Column A of sheet 2
N/A 1 1
N/A 2 2
0 3 4
N/A 4


This script works

For i = 1 To LastRow Step 1
If Cells(i, 1).Text = "#N/A" Then Worksheets(2).Range("A" & Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1) = Worksheets(1).Cells(i, 2).Value
Next i

BTW: can anyone guide me through this: Worksheets(2).Range("A" & Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1)
I copy it from stackflow but I don't understand it clearly

However, I don't understand why this one doesn't work:

Sub test()
Dim i As Integer, j As Integer




LastRow = Range("A1048576").End(xlUp).Row
j = Worksheets(2).Range("A1048576").End(xlUp).Row


For i = 1 To LastRow Step 1
If Cells(i, 1).Text = "#N/A" Then Worksheets(2).Range("A" & j + 1) = Worksheets(1).Cells(i, 2).Value


Next i
End Sub

This produce the result like:

Column A of sheet 2
4

(It doesn't have the loop one by one but the previous figure is replaced by the later one) I think I juse j to replace the long sentence of the first case but it doesn't work

 

[Fluff]

You need to increment j each time you copy something over like

For i = 1 To lastRow Step 1
   If Cells(i, 1).Text = "#N/A" Then Worksheets(2).Range("A" & j + 1) = Worksheets(1).Cells(i, 2).Value
   j = j + 1
Next i

Does col A on sheets(1) have formulas, or values?

 

[baron33]

You need to increment j each time you copy something over like

For i = 1 To lastRow Step 1
   If Cells(i, 1).Text = "#N/A" Then Worksheets(2).Range("A" & j + 1) = Worksheets(1).Cells(i, 2).Value
   j = j + 1
Next i

Does col A on sheets(1) have formulas, or values?

It is vlookup formula (but I try both formula and text (by entering manually) -- both works

 

[Fluff]

If you're interested, here's another way of doing the same thing

Sub chk()
Range("A:A").SpecialCells(xlFormulas, xlErrors).Offset(, 1).Copy Sheets("Sheet2").Range("A1")
End Sub