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]

Remove the word Set.
That's used for creating a Range Variable. But you're just looking for a number.
Also, I'd highly recommend not using Row as a variable, that's already a VBA Property.
change it to MyRow or something (likewise for Col..change to MyCol)