JKB
09-25-2014, 07:54 AM
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.
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.