Visual Basic: Get row count of a sheet in Excel
 

I have three sheets in the Excel, by default, they're called Sheet1,
Sheet2, and Sheet3.

I renamed the sheets to "MarketData", "LongTermLimits", and
"ShortTermLimits" respectively.

I want to create a VB (macro?) inside an Excel file as below:

Sub MatchShortTermLimits()
Sheet2.UsedRange.Rows.Count
End Sub

It gives me an error, "Compile error: Invalid use of property"

I changed the code to:

Sub MatchShortTermLimits()
LongTermLimits.UsedRange.Rows.Count
End Sub

It then complains "Object required"

Any advice on how to get this fixed? I only want to get the row count
of Sheet2 (renamed to "LongTermLimits").

Visual Basic: Get row count of a sheet in Excel
 

You have to do something with that value

Sub MatchShortTermLimits()
myVal = Sheet2.UsedRange.Rows.Count
End Sub

or

Sub MatchShortTermLimits()
MsgBox Sheet2.UsedRange.Rows.Count
End Sub



--
__________________________________
HTH

Bob

"Curious" wrote in message
...
I have three sheets in the Excel, by default, they're called Sheet1,
Sheet2, and Sheet3.

I renamed the sheets to "MarketData", "LongTermLimits", and
"ShortTermLimits" respectively.

I want to create a VB (macro?) inside an Excel file as below:

Sub MatchShortTermLimits()
Sheet2.UsedRange.Rows.Count
End Sub

It gives me an error, "Compile error: Invalid use of property"

I changed the code to:

Sub MatchShortTermLimits()
LongTermLimits.UsedRange.Rows.Count
End Sub

It then complains "Object required"

Any advice on how to get this fixed? I only want to get the row count
of Sheet2 (renamed to "LongTermLimits").

Visual Basic: Get row count of a sheet in Excel
 

On Mon, 6 Jul 2009 06:51:32 -0700 (PDT), Curious
wrote:

I have three sheets in the Excel, by default, they're called Sheet1,
Sheet2, and Sheet3.

I renamed the sheets to "MarketData", "LongTermLimits", and
"ShortTermLimits" respectively.

I want to create a VB (macro?) inside an Excel file as below:

Sub MatchShortTermLimits()
Sheet2.UsedRange.Rows.Count
End Sub

It gives me an error, "Compile error: Invalid use of property"

I changed the code to:

Sub MatchShortTermLimits()
LongTermLimits.UsedRange.Rows.Count
End Sub

It then complains "Object required"

Any advice on how to get this fixed? I only want to get the row count
of Sheet2 (renamed to "LongTermLimits").


Try this:

Sub MatchShortTermLimits()
Worksheets("LongTermLimits").UsedRange.Rows.Count
End Sub

Hope this helps / Lars-Åke

Visual Basic: Get row count of a sheet in Excel
 

On Mon, 06 Jul 2009 14:20:55 GMT, Lars-Åke Aspelin
wrote:

On Mon, 6 Jul 2009 06:51:32 -0700 (PDT), Curious
wrote:

I have three sheets in the Excel, by default, they're called Sheet1,
Sheet2, and Sheet3.

I renamed the sheets to "MarketData", "LongTermLimits", and
"ShortTermLimits" respectively.

I want to create a VB (macro?) inside an Excel file as below:

Sub MatchShortTermLimits()
Sheet2.UsedRange.Rows.Count
End Sub

It gives me an error, "Compile error: Invalid use of property"

I changed the code to:

Sub MatchShortTermLimits()
LongTermLimits.UsedRange.Rows.Count
End Sub

It then complains "Object required"

Any advice on how to get this fixed? I only want to get the row count
of Sheet2 (renamed to "LongTermLimits").


Try this:

Sub MatchShortTermLimits()
Worksheets("LongTermLimits").UsedRange.Rows.Count
End Sub

Hope this helps / Lars-Åke

Ooops, should be something like this

Sub MatchShortTermLimits()
myrowcount = Worksheets("LongTermLimits").UsedRange.Rows.Count
End Sub

Lars-Åke

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?

Visual Basic: Get row count of a sheet in Excel
 

On Mon, 6 Jul 2009 09:07:39 -0700 (PDT), Curious
wrote:

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?


Change
Cells(E,i) to Cells(i,"E")
Cells(J,i) to Cells(i,"J")
Cells(D,i) to Cells(i,"D")
Cells(H,i) to Cells(i,"H")
etc

Hope this helps / Lars-Åke

Visual Basic: Get row count of a sheet in Excel
 

Thanks Lars-Åke! It works!

Now I've got another error, "Type mismatch" 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).Value = "S" Then ' "Type
mismatch" error here
ActiveSheet.Cells("J", i).Value = Max(ActiveSheet.Cells
("D", i).Value, ActiveSheet.Cells("H", i).Value)
Else
If ActiveSheet.Cells("E", i).Value = "B" Then
ActiveSheet.Cells("J", i).Value = Min(ActiveSheet.Cells
("D", i).Value, ActiveSheet.Cells("H", i).Value)
End If
End If
i = i + 1
Loop

ActiveSheet.Save

End Sub

Anyone could again tell me what is wrong? Please help me!

Visual Basic: Get row count of a sheet in Excel
 

On Mon, 6 Jul 2009 10:33:37 -0700 (PDT), Curious
wrote:

Thanks Lars-Åke! It works!

Now I've got another error, "Type mismatch" 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).Value = "S" Then ' "Type
mismatch" error here
ActiveSheet.Cells("J", i).Value = Max(ActiveSheet.Cells
("D", i).Value, ActiveSheet.Cells("H", i).Value)
Else
If ActiveSheet.Cells("E", i).Value = "B" Then
ActiveSheet.Cells("J", i).Value = Min(ActiveSheet.Cells
("D", i).Value, ActiveSheet.Cells("H", i).Value)
End If
End If
i = i + 1
Loop

ActiveSheet.Save

End Sub

Anyone could again tell me what is wrong? Please help me!


You changed Cells(E,i) to Cells("E",i) but I think you should change
it to Cells(i,"E").
And the same for the other, similar, calls to Cells.

Hope this helps / Lars-Åke

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.

Visual Basic: Get row count of a sheet in Excel
 

On Mon, 6 Jul 2009 11:16:37 -0700 (PDT), Curious
wrote:

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.

Change

If ActiveSheet.Cells(i,"H").Value < "#N/A" Then

to

If Not IsError(ActiveSheet.Cells(1, "H").Value) Then

You can use the debugger to print the value of variable i when your
macro stops due to an error.
(Press CTRL+G and type ?i in the Immediate window.)

Hope this helps / Lars-Åke