Solved: Application-defined or object-defined error

[zoom38]

Can someone take a look at the sub below and advise what I did wrong. When i run the sub I get an "Application-Defined or object-defined error" message and it highlights the line with the .value and the countifs. Meanwhile when it stops and I hover over the variables the values are all correct.


Sub OneShiftTally()
Dim BW1LastRow As Long
Dim col As Integer
Dim rwIndex As Long

'Find The Last Row On The Schedule Worksheet
BW1LastRow = Cells(Rows.Count, 1).End(xlUp).Row - 5

'Place The Total Number Of One Shifts In The
'Appropriate Cell On The Bottom Of The Schedule
For col = 4 To 31
rwIndex = Cells(Rows.Count, 1).End(xlUp).Row - 3
With Worksheets(1).Cells(rwIndex, col)
.Value = Application.WorksheetFunction.CountIf(Range(Cells(6, col), Cells(BW1LastRow, col)), "1(5)") + _
Application.WorksheetFunction.CountIf(Range(Cells(6, col), Cells(BW1LastRow, col)), "1(6)") + _
Application.WorksheetFunction.CountIf(Range(Cells(6, col), Cells(BW1LastRow, col)), "1") + _
Application.WorksheetFunction.CountIf(Range(Cells(6, col), Cells(BW1LastRow, col)), "1(8)") + _
Application.WorksheetFunction.CountIf(Range(Cells(6, col), Cells(BW1LastRow, col)), "1(9)") + _
Application.WorksheetFunction.CountIf(Range(Cells(6, col), Cells(BW1LastRow, col)), "1/AD") + _
Application.WorksheetFunction.CountIf(Range(Cells(6, col), Cells(BW1LastRow, col)), "1/CE") + _
Application.WorksheetFunction.CountIf(Range(Cells(6, col), Cells(BW1LastRow, col)), "1/DWI") + _
Application.WorksheetFunction.CountIf(Range(Cells(6, col), Cells(BW1LastRow, col)), "1/SB") + _
Application.WorksheetFunction.CountIf(Range(Cells(6, col), Cells(BW1LastRow, col)), "1/SPD")
End With
Next col
End Sub



Thanks
Gary

[geekgirlau]

Often a good way of analysing where the problem is, is to break it down into small chunks. So rather than trying to tackle it in one step, do it in several steps. For example:


lngValue = Application.WorksheetFunction.CountIf(Range(Cells(6, col), _
Cells(BW1LastRow, col)), "1(5)")
lngValue = lngValue + _
Application.WorksheetFunction.CountIf(Range(Cells(6, col), _
Cells(BW1LastRow, col)), "1(6)")

' etc.

Worksheets(1).Cells(rwIndex, col).Value = lngValue


This should enable you to work out where the problem is.

[zoom38]

Im no longer getting the "Application-Defined or object-defined error" but I am getting an "Out Of Stack Space" error message. This sub is in Module4a and is called from Sheet1 in a Worksheet Change event.


Sub ShiftTallyMain()
Dim BW1LastRow As Integer

Application.ScreenUpdating = False
ActiveSheet.Unprotect
Call OneShiftTally(BW1LastRow)
' Call TwoShiftTally(BW1LastRow)
' Call FlexShiftTally(BW1LastRow)
ActiveSheet.Protect
Application.ScreenUpdating = True
End Sub

Sub OneShiftTally(BW1LastRow)
Dim col As Integer
Dim rwIndex As Long
'Find The Last Row On The Schedule Worksheet
BW1LastRow = Cells(Rows.Count, 1).End(xlUp).Row - 5

'Place The Total Number Of One Shifts In The
'Appropriate Cell On The Bottom Of The Schedule
For col = 4 To 31
rwIndex = (Cells(Rows.Count, 1).End(xlUp).Row) - 3
With Worksheets(1).Cells(rwIndex, col)
.Value = Application.WorksheetFunction.CountIf(Range(Cells(6, col), Cells(BW1LastRow, col)), "1(5)") + _
Application.WorksheetFunction.CountIf(Range(Cells(6, col), Cells(BW1LastRow, col)), "1(6)") + _
Application.WorksheetFunction.CountIf(Range(Cells(6, col), Cells(BW1LastRow, col)), "1") + _
Application.WorksheetFunction.CountIf(Range(Cells(6, col), Cells(BW1LastRow, col)), "1(8)") + _
Application.WorksheetFunction.CountIf(Range(Cells(6, col), Cells(BW1LastRow, col)), "1(9)") + _
Application.WorksheetFunction.CountIf(Range(Cells(6, col), Cells(BW1LastRow, col)), "1/AD") + _
Application.WorksheetFunction.CountIf(Range(Cells(6, col), Cells(BW1LastRow, col)), "1/CE") + _
Application.WorksheetFunction.CountIf(Range(Cells(6, col), Cells(BW1LastRow, col)), "1/DWI") + _
Application.WorksheetFunction.CountIf(Range(Cells(6, col), Cells(BW1LastRow, col)), "1/SB") + _
Application.WorksheetFunction.CountIf(Range(Cells(6, col), Cells(BW1LastRow, col)), "1/SPD")
End With
Next col
End Sub



Can anyone advise what I am doing wrong.

Thanks
Gary

[geekgirlau]

Have you tried breaking it down as I suggested above?

[Bob Phillips]

Also, dot qualify all Range and Cells properties



.Value = Application.WorksheetFunction.CountIf(.Range(.Cells(6, col), .Cells(BW1LastRow, col)), "1(5)") + _

[zoom38]

Yes GG I did break it down. It seems to work now, I think the problem was with the protection of the sheet. XLD I'll give that a try. I also ran into a problem that it would stay in a continuous loop until I added Application.EnableEvents = False

My problem now lies in the following formula

ElseIf Cells(x, c).Value = "1*" And Cells(x, 33).Value = 2 Then
Recruit = Recruit + 1


in the following code. It doesn't seem to recognize the wildcard character "*" . Do you have any Idea on how to make that work?


For c = 4 To 31
Application.EnableEvents = False

For x = 6 To LastRow
If (Cells(x, c).Value = "1") And Cells(x, 33).Value = 2 Then
Recruits = Recruits + 1
ElseIf Cells(x, c).Value = "1(*)" And Cells(x, 33).Value = 2 Then
Recruits = Recruits + 1

End If
Next x

OneShifts = Application.WorksheetFunction.CountIf(Range(Cells(6, c), Cells(LastRow, c)), "1(5)")
OneShifts = OneShifts + Application.WorksheetFunction.CountIf(Range(Cells(6, c), Cells(LastRow, c)), "1(6)")
OneShifts = OneShifts + Application.WorksheetFunction.CountIf(Range(Cells(6, c), Cells(LastRow, c)), "1")
OneShifts = OneShifts + Application.WorksheetFunction.CountIf(Range(Cells(6, c), Cells(LastRow, c)), "1(8)")
OneShifts = OneShifts + Application.WorksheetFunction.CountIf(Range(Cells(6, c), Cells(LastRow, c)), "1(9)")
OneShifts = OneShifts + Application.WorksheetFunction.CountIf(Range(Cells(6, c), Cells(LastRow, c)), "1/AD")
OneShifts = OneShifts + Application.WorksheetFunction.CountIf(Range(Cells(6, c), Cells(LastRow, c)), "1/CE")
OneShifts = OneShifts + Application.WorksheetFunction.CountIf(Range(Cells(6, c), Cells(LastRow, c)), "1/DWI")
OneShifts = OneShifts + Application.WorksheetFunction.CountIf(Range(Cells(6, c), Cells(LastRow, c)), "1/SB")
OneShifts = OneShifts + Application.WorksheetFunction.CountIf(Range(Cells(6, c), Cells(LastRow, c)), "1/SPD")


Cells(rwIndex, c).Value = OneShifts - Recruits

Application.EnableEvents = True
Next c
Columns("AG").Hidden = True
Application.ScreenUpdating = True




Thanks
Gary

[Bob Phillips]

Wildcards don't work like that in VBA



ElseIf Cells(x, c).Value Like "1*" And Cells(x, 33).Value = 2 Then
Recruit = Recruit + 1

[zoom38]

Thanks XLD i'll give that a try. Since there were only 7 cases I coded in each case but i will try your way so it will only be one line.

Gary