Excel vba - for each cell in selection

Try

Set ws1 = ThisWorkbook.Sheets("Source")
Set ws2 = ThisWorkbook.Sheets("Destination")
With ws1
    finalrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For Each c In Range(.Cells(2, 2), .Cells(finalrow, 3))
            If IsNumeric(c) Then
                c.Value = 1 * c.Value
            End If
    Next c
End With

If you need to specify a worksheet object (which is good practice), you need to specify it for all of the Range/Cells properties you use.

So this is incorrect:

ws1.Range(Cells(2, "B"), Cells(finalrow, "C")).Select

because the two Cells properties do not have a worksheet specified.

Although this may work some of the time, it will be dependent on where the code is (in a worksheet code module, unlike other modules, any use of Range or Cells without a worksheet object reference refers to the sheet containing the code and not the active sheet) and which worksheet is active at the time so sometimes it will fail – this kind of bug can be very hard to track down.

The correct syntax for the above is:

ws1.Range(ws1.Cells(2, "B"), ws1.Cells(finalrow, "C")).Select

or you can use a With … End With block to save a little typing:

With ws1
   .Range(.Cells(2, 2), .Cells(finalrow, 3))
End With

Note the full stops (periods) before the Range and Cells calls.

Got this from here.