Difference between Range("D":D" & LastRow) and Range(Cells(2, 4), Cells(LastRow, 4)): rules of use?

[HankJ]

Hello

I would really like to understand what determines the use of these two bits of code.

This works fine:
[TABLE="width: 1190"]
<colgroup><col></colgroup><tbody>[TR]
[TD] wbTSS.Sheets("Sheet1").Range("D2:D" & LastRow).FormulaR1C1 = _[/TD]
[/TR]
[TR]
[TD] "=IF(RC[1]="""","""",VLOOKUP(RC[1],'[Dundee Assay building.xlsx]Kinase grouping'!C2:C6,5,FALSE))"

but when I used:

[TABLE="width: 493"]
<tbody>[TR]
[TD="width: 493"] wbTSS.Sheets("Sheet1").Range(Cells(2, 4), Cells(LastRow, 4)).FormulaR1C1 = _[/TD]
[/TR]
</tbody>[/TABLE]

I get a Run-Time error '1004':
Application-define or object-defined error.

Are there rules for when to use Cells(x,x)?

Many thanks

Hankj






[/TD]
[/TR]
</tbody>[/TABLE]

 

[RickXL]

Hi,

If you do not qualify your Cells references Excel will assume that you mean use the ActiveSheet. So your statement translates to:

    wbTSS.Sheets("Sheet1").Range(ActiveSheet.Cells(2, 4), ActiveSheet.Cells(LastRow, 4)).FormulaR1C1 = ""

If wbTSS.Sheets("Sheet1") was not the ActiveSheet at the time then you would get an error. The full version would be:

    wbTSS.Sheets("Sheet1").Range(wbTSS.Sheets("Sheet1").Cells(2, 4), wbTSS.Sheets("Sheet1").Cells(LastRow, 4)).FormulaR1C1 = ""

This is one reason why people use the With/End With construct:

    With wbTSS.Sheets("Sheet1")
        .Range(.Cells(2, 4), .Cells(LastRow, 4)).FormulaR1C1 = ""
    End With

My personal choice would be to use Worksheets and not Sheets. This is because Sheets includes not only the Worksheets but all the Chart sheets as well. OK, this is seldom a problem but sometimes when you loop round a Sheets Collection it can give errors as you try to read a cell on a Chart sheet.

So I would use:

    With wbTSS.Worksheets("Sheet1")
        .Range(.Cells(2, 4), .Cells(LastRow, 4)).FormulaR1C1 = ""
    End With

I hope that makes sense.

Regards,

 

[HankJ]

Hi RickXL

Many thanks for your response. I've not had time to run it through yet but will do later today.
Been banging my head against the wall with this one over the weekend and sometime before that.

Yours
Hj

 

[HankJ]

Hi RickXL

That indeed worked a treat.

Could I ask if there are any limitations to this type of referencing, so is it the way I should go with all sheet IDs?

Many thanks for taking the time for this.

Yours

Hank

 

[RickXL]

Eventually, it comes down to personal choice. If you only ever use one WorkSheet at a time then you can opt for the shortest way of referencing cells possible.

However, that would not be my choice. There are many ways to reference a Range in Excel VBA and they have different uses. For instance, if I were to write a utility macro that, say, highlighted the column with the ActiveCell in yellow then I might want that to work in every Workbook and every Worksheet. In that case I would use something like:

Sub col()
    Columns(ActiveCell.Column).Interior.ColorIndex = 6
End Sub

There is no qualification of Columns at all. That means that I can assign it to, for instance, a Quick Access Toolbar button and I could use it anywhere.

Alternatively, if I were writing a particular macro that would only ever be run against data in the same Workbook as the macro and on Sheet1 in that Workbook I would use something like:

Sub mySheet()
    ThisWorkbook.Worksheets("Sheet1").Range("C2:E5") = 999
End Sub

That will fill those cells on that particular Worksheet every time. If you don't do that and you just say:

Sub mySheet()
    Activesheet.Range("C2:E5") = 999
End Sub

Then any Worksheet that happens to be active whether in the Workbook you intended or not will be overwritten.

Another option, If I were writing an Add-In that will work for any Workbook then I would probably use ActiveSheet because then the Add-In would work more like a utility across many Workbooks.

Many people never specify ThisWorkbook, I notice. That leaves them open to issues when the user opens a second Workbook. The macro might run in the wrong one.

I tend towards the paranoid side. I like to specify as closely as I can which sheet the macro should be using.

There are other ways of defining ranges. My advice would be to try some and see what works best for you. If I am using several Workbooks and Worksheets at the same time then I tend to give them shorter names. So my code might start like this:

Sub MultiBook()
    Dim WB1 As Workbook
    Dim WB2 As Workbook
    Dim WB3 As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim ws3 As Worksheet
    Dim ws4 As Worksheet
    
    Set WB1 = ThisWorkbook
    Set WB2 = Workbooks.Open("C:\Data\Excel1.xlsx")
    Set WB2 = Workbooks.Open("C:\Data\Excel2.xlsx")
    
    Set ws1 = WB1.Worksheets("Input")
    Set ws2 = WB1.Worksheets("Lookup")
    Set ws3 = WB2.Worksheets("ThisMonth")
    Set ws4 = WB3.Worksheets("Archive")

    ws3.Range("A33").Resize(5, 2) = ws1.Range("A2").Resize(5, 2)
    
    ' etc
    ' etc
    
End Sub

That way of doing things is quite useful for testing. You can just change one of the assignments at the start of the code and that will enable you to test the code using non-productive data quite easily.

I am starting to ramble. Please come back if you have any questions.

I hope this helps.

Regards,

 

[HankJ]

Eventually, it comes down to personal choice. If you only ever use one WorkSheet at a time then you can opt for the shortest way of referencing cells possible.

However, that would not be my choice. There are many ways to reference a Range in Excel VBA and they have different uses. For instance, if I were to write a utility macro that, say, highlighted the column with the ActiveCell in yellow then I might want that to work in every Workbook and every Worksheet. In that case I would use something like:

Sub col()
    Columns(ActiveCell.Column).Interior.ColorIndex = 6
End Sub

There is no qualification of Columns at all. That means that I can assign it to, for instance, a Quick Access Toolbar button and I could use it anywhere.

Alternatively, if I were writing a particular macro that would only ever be run against data in the same Workbook as the macro and on Sheet1 in that Workbook I would use something like:

Sub mySheet()
    ThisWorkbook.Worksheets("Sheet1").Range("C2:E5") = 999
End Sub

That will fill those cells on that particular Worksheet every time. If you don't do that and you just say:

Sub mySheet()
    Activesheet.Range("C2:E5") = 999
End Sub

Then any Worksheet that happens to be active whether in the Workbook you intended or not will be overwritten.

Another option, If I were writing an Add-In that will work for any Workbook then I would probably use ActiveSheet because then the Add-In would work more like a utility across many Workbooks.

Many people never specify ThisWorkbook, I notice. That leaves them open to issues when the user opens a second Workbook. The macro might run in the wrong one.

I tend towards the paranoid side. I like to specify as closely as I can which sheet the macro should be using.

There are other ways of defining ranges. My advice would be to try some and see what works best for you. If I am using several Workbooks and Worksheets at the same time then I tend to give them shorter names. So my code might start like this:

Sub MultiBook()
    Dim WB1 As Workbook
    Dim WB2 As Workbook
    Dim WB3 As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim ws3 As Worksheet
    Dim ws4 As Worksheet
    
    Set WB1 = ThisWorkbook
    Set WB2 = Workbooks.Open("C:\Data\Excel1.xlsx")
    Set WB2 = Workbooks.Open("C:\Data\Excel2.xlsx")
    
    Set ws1 = WB1.Worksheets("Input")
    Set ws2 = WB1.Worksheets("Lookup")
    Set ws3 = WB2.Worksheets("ThisMonth")
    Set ws4 = WB3.Worksheets("Archive")

    ws3.Range("A33").Resize(5, 2) = ws1.Range("A2").Resize(5, 2)
    
    ' etc
    ' etc
    
End Sub

That way of doing things is quite useful for testing. You can just change one of the assignments at the start of the code and that will enable you to test the code using non-productive data quite easily.

I am starting to ramble. Please come back if you have any questions.

I hope this helps.

Regards,

Hi RickXL
Many thanks for your "ramble", it has been really helpful for me. Sorry for the delay in responding, I went away on holiday for two weeks and managed to leave my work completely alone...

Yours
Hankj

 

[RickXL]

You're welcome.

I hope you enjoyed your break..


Regards,