Excel vba - for each cell in selection [duplicate]

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-02 15:22:35Z

2

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.

edited Sep 2, 2017 at 15:22

answered Sep 2, 2017 at 14:40

Mrig

11.7k2 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

crni Over a year ago

It returns the same error 1004 "Application-defined or object-defined error"

Mrig Over a year ago

@crni - See updated code now.

crni Over a year ago

Yes, that worked. Thanks. It took me awhile to enter it correctly. But what was the reason my didn't work.

Mrig Over a year ago

@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 Over a year ago

@crni - I've added explanation in the answer.

Collectives™ on Stack Overflow

Excel vba - for each cell in selection [duplicate]

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related