Sub rollforward()
Dim ua As Worksheet: Set ua = Sheets("Unit Activity Adj Tab")
Dim ws As Worksheet: Set ws = Sheets("Assumptions")
ua.Range("G1:CX1").Find(Application.WorksheetFunction.EoMonth(ws.Range("B1"),0)).Interior.Color = RGB(198, 239, 206)
End Sub
This will not work! I can’t figure it out. I keep getting a error 91: Object Variable or With block variable not set
>Solution :
I’ll try to break down the code to be a little more manageable:
Sub rollforward()
Dim ua As Worksheet: Set ua = Sheets("Unit Activity Adj Tab")
Dim ws As Worksheet: Set ws = Sheets("Assumptions")
Dim findTerm As String: findTerm = Format(Application.WorksheetFunction.EoMonth(ws.Range("B1").Value,0), "mm/dd/yyyy") 'UPDATE FOR YOUR FORMAT
Dim findRange As Range: Set findRange = ua.Range("G1:CX1").Find(findTerm) 'Add looks ins, etc.
If findRange Is Nothing Then Exit Sub
findRange.Interior.Color = RGB(198, 239, 206)
End Sub
Couple things I’ve done:
- Separated out the term you’re finding. You may need some validation for the input on this to ensure
EoMonth()
can do its job. - Added
.Value
to your search value to ensure you’re using only the value. - Added an error check for your
Find()
in case something isn’t found. - Added an action on the found range after the error check.
Now, a couple of things… you may need to format your output… Debug.Print Application.EoMonth(Now(), 0)
outputs 44804
. You may have trouble finding that, and may need to verify that your find range is not in strings, etc.
Edit1: Added format()
to findTerm
which should allow the appropriate finding.