Excel vba - for each cell in selection

itemprop="text">

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.

class="post-text" itemprop="text">

class="normal">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.