VBA Count Function

[trough]

Not new to programming but new to vba. Macro gets hung up on highlighted line SOMETIMES. It will work and then it won't. Really puzzled. Tried some things I found in forums but nothing has been a permanent fix. Solutions?

Sub RotLoad()

' macro to cycle through all loadcases
' and obtain rotated loads at all load
' points for all load cases

Dim num_lpts As Integer
Dim num_lc As Integer
Dim part_num As String
Dim calc_sheet As String
Dim load_sheet As String
Dim rot_sheet As String

' prompt for sheet prefix specific to fitting being analyzed
part_num = InputBox("Enter part prefix:")

' store sheet names used
calc_sheet = part_num & "_Analysis"
load_sheet = part_num & "_Global_Loads"
rot_sheet = part_num & "_Rotated_Loads"

' store number of load points and number of load cases
num_lpts = Sheets(calc_sheet).Cells(79, 4)
num_lc = Application.WorksheetFunction.Count(Sheets(load_sheet).Range(Cells(10, 1), Cells(9999, 1)))

' set load case number then store rotated loads for each load point for each load case
For i = 1 To num_lc
Sheets(calc_sheet).Cells(188, 3) = i
For j = 1 To num_lpts
Sheets(rot_sheet).Cells(10 - 1 + i, 3 * j + 1) = Sheets(calc_sheet).Cells(195 - 1 + j, 12)
Sheets(rot_sheet).Cells(10 - 1 + i, 3 * j + 1) = Sheets(calc_sheet).Cells(195 - 1 + j, 13)
Sheets(rot_sheet).Cells(10 - 1 + i, 3 * j + 1) = Sheets(calc_sheet).Cells(195 - 1 + j, 14)
Next j
Next i

End Sub

 

[Jonmo1]

Try

num_lc = Application.WorksheetFunction.Count(Sheets(load_sheet).Range("A10:A9999"))

 

[BobUmlas]

If user enters 888 for the input box, then this line:
load_sheet = part_num & "_Global_Loads"
will wind up being
load_sheet = "888_Global_Loads"

and that load_sheet may not exist, hence your error.

 

[tusharm]

You've already received a couple of possible solutions but you've not shared what the problem is. What does "hung up" mean? Do you get an error message? if so, what?

Not new to programming but new to vba. Macro gets hung up on highlighted line SOMETIMES. It will work and then it won't. Really puzzled. Tried some things I found in forums but nothing has been a permanent fix. Solutions?

Sub RotLoad()

' macro to cycle through all loadcases
' and obtain rotated loads at all load
' points for all load cases

Dim num_lpts As Integer
Dim num_lc As Integer
Dim part_num As String
Dim calc_sheet As String
Dim load_sheet As String
Dim rot_sheet As String

' prompt for sheet prefix specific to fitting being analyzed
part_num = InputBox("Enter part prefix:")

' store sheet names used
calc_sheet = part_num & "_Analysis"
load_sheet = part_num & "_Global_Loads"
rot_sheet = part_num & "_Rotated_Loads"

' store number of load points and number of load cases
num_lpts = Sheets(calc_sheet).Cells(79, 4)
num_lc = Application.WorksheetFunction.Count(Sheets(load_sheet).Range(Cells(10, 1), Cells(9999, 1)))

' set load case number then store rotated loads for each load point for each load case
For i = 1 To num_lc
Sheets(calc_sheet).Cells(188, 3) = i
For j = 1 To num_lpts
Sheets(rot_sheet).Cells(10 - 1 + i, 3 * j + 1) = Sheets(calc_sheet).Cells(195 - 1 + j, 12)
Sheets(rot_sheet).Cells(10 - 1 + i, 3 * j + 1) = Sheets(calc_sheet).Cells(195 - 1 + j, 13)
Sheets(rot_sheet).Cells(10 - 1 + i, 3 * j + 1) = Sheets(calc_sheet).Cells(195 - 1 + j, 14)
Next j
Next i

End Sub

 

[trough]

Sorry, I've been in a meeting.

The macro stops and goes into debug mode when it hits the highlighted line but not always. Don't know what triggers it but sometimes it runs perfectly and other times not. Once it stops working it doesn't seem to start working again until I do some editing of the line trying different things then I'll go back to my original code on that line and it will work for awhile.

The sheet does exist. I've checked that the other variables have the correct value.

The A10:A9999 method works but I would prefer to use the row,column reference style.

Error: Run -time error '1004'
Application-defined or object-defined error
num_lc=0 should be 789

 

[Jonmo1]

OK, the reason it fails with the Cells function is because...

When you use this syntax
Range(Cells(..,..), Cells(..,..))
And you specify the sheet on the RANGE, then you must also specify it on the Cells

Instead of
Sheets("sheetname").Range(Cells(..,..), Cells(..,..))
You need
Sheets("sheetname").Range(Sheets("sheetname").Cells(..,..), Sheets("sheetname").Cells(..,..))

or

With Sheets("Sheetname")
.Range(.Cells(..,..), .Cells(..,..))
End With


The reason it works sometimes, is if the Sheet specified just happens to be the currently active sheet, then the Cells is referring to the same sheet.
But if say Sheet10 is currently active, but range is referring to Sheet1, then it fails because Range is referring to Sheet1, but the Cells is referring to Sheet10.

Hope that helps.

 

[trough]

Most excellent. That works except for the "or" part. I had tried that earlier to no avail.

Thanks.

 

[bkelly]

With Sheets("Sheetname")
.Range(.Cells(..,..), .Cells(..,..))
End With

Most excellent. That works except for the "or" part. I had tried that earlier to no avail.

Thanks.

Make sure you have your WITH statement set right when you use the dot notation.

 

[trough]

Oh! Got it.

Again, new to vba. Need to get me a vba book.


Thanks for explaining all of it.

 

[kmgtij]

I have the issue but inspite of following the suggestions in this thread the problem persits. It does not compile with the folowing error:

Compile Error: Object Required

Your help will be appreciated!
Sub Macro1()


Dim Row As Integer, Col As Integer


For Col = 10 To 280
With Sheets("XYZ")
Set Row = Application.WorksheetFunction.Count(.Range(.Cells(1, Col)), .Range(.Cells(1200, Col)))
End With
Sheets("Summary").Cells(((Col - 2) / 5) + 1, 5) = Sheets("XYZ").Cells(Row - 1, Col).Value

Col = Col + 5
Next Col

End Sub