Need help with \"Object variable or With block variable not set\" error in VBA c
ID: 3562492 • Letter: N
Question
Need help with "Object variable or With block variable not set" error in VBA code
I am trying to figure out why I get an error on the second "Set" statement. The first "Set" statement is is fine, except I need the flexibility to be able to determine what the end column is.
Set cel = Range("B9:CS" & qplCol).Find(What:="END OF MONTH FORECAST", LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
EOMonth_1 = cel.Column
Set cel = Range(Cells(9,2), Cells(9, qplCol)).Find(What:="END OF MONTH FORECAST", LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
EOMonth_1 = cel.Column
Set cel = Range(Cells(9, EOMonth_1 + 1), Cells(9, qplCol)).Find(What:="END OF MONTH FORECAST", LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
EOMonth_2 = cel.Column
Set cel = Range(Cells(9, EOMonth_2 + 1), Cells(9, qplCol)).Find(What:="END OF MONTH FORECAST", LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
EOMonth_3 = cel.Column
Even when I try using this approach:
rngFind = Range(Cells(9, EOMonth_1 + 1), Cells(9, qplCol)).Address
Set cel = Range(rngFind).Find(What:="END OF MONTH FORECAST", LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
EOMonth_2 = cel.Column
I still get the "Object variable or With block variable not set" error. And ideas will be greatly appreciated. This is my latest iteration of the Sub:
Sub daily_EndOfMonth_Total()
Dim EOMonth_1 As Long, EOMonth_2 As Long, EOMonth_3 As Long
Dim i As Long
Dim rngFind As String
'==============================================================================
QPDws.Select
qplRow = Cells(Rows.Count, 2).End(xlUp).Row
qplCol = Cells(11, Columns.Count).End(xlToLeft).Column
'----------------------------------------------------------------------
rngFind = Range(Cells(9, 2), Cells(9, qplCol)).Address
Set cel = Range(rngFind).Find(What:="END OF MONTH FORECAST", LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
EOMonth_1 = cel.Column
rngFind = Range(Cells(9, EOMonth_1 + 1), Cells(9, qplCol)).Address
Set cel = Range(rngFind).Find(What:="END OF MONTH FORECAST", LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
EOMonth_2 = cel.Column
rngFind = Range(Cells(9, EOMonth_2 + 1), Cells(9, qplCol)).Address
Set cel = Range(rngFind).Find(What:="END OF MONTH FORECAST", LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
EOMonth_3 = cel.Column
'----------------------------------------------------------------------
For i = 12 To 47
Cells(i, EOMonth_1) = WorksheetFunction.Sum(Range(Cells(i, 3), Cells(i, EOMonth_1 - 1)))
Cells(i, EOMonth_2) = WorksheetFunction.Sum(Range(Cells(i, EOMonth_1 + 1), Cells(i, EOMonth_2 - 1)))
Cells(i, EOMonth_3) = WorksheetFunction.Sum(Range(Cells(i, EOMonth_2 + 1), Cells(i, EOMonth_3 - 1)))
Next i
'----------------------------------------------------------------------
Range("C12").Select
End Sub
Explanation / Answer
Your code will generate an error each time the Find command finds nothing because you are then trying to assign that to a range variable (Set Cel =).
To avoid that, try something like:
'Ignore errors
On Error Resume Next
Set Cel = 'first find
If Cel Is Nothing Then
Set Cel = 'Second find
If Cel Is Nothing Then
Set Cel = 'Third find
End If
End If
On Error Goto 0
If Not Cel Is Nothing Then
'Now use Cel
Else
'Item was not found
End If