Excel vba - for each cell in selection

This is just a part of my code:

Set ws1 = Sheets("Source")
Set ws2 = Sheets("Destination")

finalrow = ws1.Cells(Rows.Count, "A").End(xlUp).Row

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

For Each c In Selection
        If IsNumeric(c) Then

            c.Value = 1 * c.Value
        End If
Next c

Even though, I specified sheets "Source" and "Destination", if I don't
run that macro from the sheet "Source", it returns an error. I thought the problem is with the line "For Each c In Selection" but it points to row above as the problematic one. I don't know what is the problem with that.

Answer

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.