Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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"). |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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"). |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is Visual Basic the same as Visual Studio 2008? | Excel Worksheet Functions | |||
Visual Basic code not working when sheet is saved | Excel Discussion (Misc queries) | |||
Visual Basic - Variable Sheet Names | Excel Discussion (Misc queries) | |||
visual basic. find copy row past into new sheet | Excel Worksheet Functions | |||
changing the visual basic in office 2003 to visual studio net | Excel Discussion (Misc queries) |