0
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

B-Rye
  • 72
  • 1
  • 8
  • 2
    When using `Find`, *always* specify the `What`, `LookIn`, and `LookAt` parameters, and *always* [test if the Find succeeded](https://stackoverflow.com/questions/1589939/how-to-detect-whether-vba-excel-found-something). Also `Find` can be tricky with dates. – BigBen Aug 05 '22 at 14:26
  • I've even tried: ua.Range("G1:CX1").Find(ws.Range("B1")).Interior.Color = RGB(198, 239, 206) But I get the same error – B-Rye Aug 05 '22 at 14:29
  • Now that I know you're working with formulae in that range, as opposed to actual dates... See [Range.Find on a Date That is a Formula](https://stackoverflow.com/a/45641402/3233363) – Cyril Aug 05 '22 at 14:45

1 Answers1

2

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.

Cyril
  • 6,448
  • 1
  • 18
  • 31
  • I'm getting the same error, even when I remove the function and just replace it with findTerm = ws.Range("B1"). The range("G1:CX1") are EOMONTH functions. Is it possible that even though the date is being produced, since there's functions in the cells it's having trouble finding the date? – B-Rye Aug 05 '22 at 14:39
  • If I replace the ws.Range("B1") with "7/31/2022" then it works but I can't get the range to work. I'm also trying the range with .Value. – B-Rye Aug 05 '22 at 14:41
  • @B-Rye I was just updated my code, which now includes `format()` in the `findTerm`... sorry, was working on that in Excel, because the formatting is very important as it can't find the generic date. – Cyril Aug 05 '22 at 14:42
  • @B-Rye Just posted a link to Ron's loop for this, as it I believe your question has already been answered. – Cyril Aug 05 '22 at 14:47
  • That was it! Thanks! I was missing for Format() – B-Rye Aug 05 '22 at 14:49