Choosing ranges/cells between sheets!

[JKB]

Hi out there!

Im not entirely sure i understand how to choose ranges/cells in different sheets. Fx if im running my sheet in "Sheet1", how do i select a range in sheet 2 from there?

What ive done is the following.


Dim sht1 As Worksheet
Dim sht2 As Worksheet
Set sht1 = ActiveWorkbook.Sheets(1)
Set sht2 = ActiveWorkbook.Sheets(2)
Dim iLastRow As Integer
Dim iLastCol As Integer
iLastRow = sht1.Range("A1").End(xlDown).Row - 1
iLastCol = sht1.Range("A1").End(xlToRight).Column - 3
Dim i As Integer
Dim k As Integer
For k = 0 To iLastCol - 2
For i = 2 To iLastRow
sht1.Range("D2").Activate ' Will NOT run when im in sheet 2.
sht2.Cells(i, k + 4).Value = (sht1.Cells(i, k + 4).Offset(0, 1).Value - sht1.Cells(i, k + 4).Value) / Cells(i, k + 4).Value
Next i
Next k
sht2.range(sht2.Cells(2, 4), sht2.Cells(iLastRow, iLastCol)).Select 'Will NOT run when im in sheet 1.
End Sub

It makes sense that "Sheet1" must be the activated sheet, since the code is written in sheet 1! The loop is working fine (Despite the fact that im programming in cells in sheet2)



But what i dont get is the two bold sentences, these cause me a lot of trouble!

I get the "application defined - or object defined error" when i try to run the program... How can i run the program without getting this error? I thought the
sht1. and sht2.-prefixes would take care of this problem for me? :)
Hope someone can help! As you can see, my problem clearly is that im trying to be in 2 sheets at the same time, but i thought the prefixes would help!

Cheers Jacob.

[lecxe]

Hi Jacob

It's bad practice to select/activate ranges in vba. It makes the code less efficient and more difficult to read.

In the specific case of the code you posted why would you want to activate the range?

For ex.:


sht2.Cells(i, k + 4).Value = (sht1.Cells(i, k + 4).Offset(0, 1).Value - sht1.Cells(i, k + 4).Value) / Cells(i, k + 4).Value

This is perfect. You are working with ranges in 2 different worksheets and, as you see, you don't need to activate anything.

Try running it without the activate

Remark: You forgot to use the worksheet prefix in the last Cells() , after the "/". Please add it.

[JKB]

Ah i suppose thats a very good point!!!!
It was something like i thought the loop had to start from where i activated, but ofc. its already in the code!

And now it makes sense with the loop sentence, nice! Thank you very much!
If you by chance have noticed anything else which is bad practice, i would be happy to know! :)

But thank you very much for helping me!

Cheers Jacob.

[lecxe]

Hi Jacob

I'm glad it helped. Thanks for the feedback.




If you by chance have noticed anything else which is bad practice, i would be happy to know! :)


Well, I did notice one. It's not a good idea to dimension the Row variable as an Integer.

As you may know, the maximum positive Integer is 32k-1 and a worksheet has 1M rows.
This means an Integer variable will sometimes not be able to hold the row number.

You should always dimension the Row variable as Long. It avoids bad surprises.

As a matter of fact, I usually define both row and column variables as Long.



Dim lLastRow As Long
Dim lLastCol As Long

[JKB]

That is a good point as well actually! :) The reason for why i know that it will not be that much of in this particular case is that the code is based on a scraper where it will max download 30-40 stock prices! :) But i will definitely define it as a long, the next time!

[lecxe]

Great. Cheers! :)