Range.Find(Function.Range)

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.