Visual Basic: Get row count of a sheet in Excel
Thanks Lars-Åke! It works.
It works fine until it loops to a row with column H whose value is "#N/
A", where I get a "Type mismatch" error at runtime. I thought I had
filered out these rows by using an IF statement ("If ActiveSheet.Cells
(i, "H").Value < "#N/A" Then" ).
Anyway, my code is 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
If ActiveSheet.Cells(i, "E").Value = "S" Then
If ActiveSheet.Cells(i, "H").Value < "#N/A" Then
ActiveSheet.Cells(i, "J").Value = Max(ActiveSheet.Cells
(i, "D").Value, ActiveSheet.Cells(i, "H").Value)
End If
Else
If ActiveSheet.Cells(i, "E").Value = "B" Then
If ActiveSheet.Cells(i, "H").Value < "#N/A" Then
ActiveSheet.Cells(i, "J").Value = Min
(ActiveSheet.Cells(i, "D").Value, ActiveSheet.Cells(i, "H").Value)
End If
End If
End If
i = i + 1
Loop
ActiveSheet.Save
End Sub
How to resolve the issue about a column having both numeric and text
value? Also how can I know at which line I get the "Type mismatch
error"? Please help me.
|