Excel vba - for each cell in selection

Question

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.

Mrig · Accepted Answer · 2017-09-02T15:22:35.537

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.

It returns the same error 1004 "Application-defined or object-defined error" — crni, Sep 02 '17 at 14:43
Yes, that worked. Thanks. It took me awhile to enter it correctly. But what was the reason my didn't work. — crni, Sep 02 '17 at 14:53
@crni - As in `ws1.Cells(Rows.Count, "A").End(xlUp).Row` though your using `ws1.Cells` but `Rows.Count, "A"` will refer to the active sheet and not `ws1`. — Mrig, Sep 02 '17 at 14:57

Excel vba - for each cell in selection

1 Answers1