Visual Basic: Get row count of a sheet in Excel
Thanks to all who provided their help! Now I have the following script
and got an "application-defined or object-defined" error at the line
marked below:
Sub MatchShortTermLimits()
myrowcount = Worksheets("LongTermLimits").UsedRange.Rows.Count
Sheets("LongTermLimits").Select
Dim r As Range, cell As Range
With ActiveSheet
Set r = .Range(.Cells(2, "D"), .Cells(myrowcount + 1, "D").End
(xlToLeft))
End With
i = 2
Do While i <= myrowcount + 1
If ActiveSheet.Cells(E, i) = "S" Then ' "application-
defined or object-defined" error
ActiveSheet.Cells(J, i).Value = Max(ActiveSheet.Cells(D,
i), ActiveSheet.Cells(H, i))
Else
If ActiveSheet.Cells(E, i) = "B" Then
ActiveSheet.Cells(J, i).Value = Min(ActiveSheet.Cells
(D, i), ActiveSheet.Cells(H, i))
End If
End If
i = i + 1
Loop
ActiveSheet.Save
End Sub
It seems that "ActiveSheet.Cells(E, i)" causes the problem. Any input?
|