0

I'm no expert in VBA (currently teaching myself) and I was hoping someone could help with an error I'm facing. I'm currently working on a macro to go to today's date that is located somewhere in one of the worksheets that function as weekly timesheets. Each timesheet is based on the same template. I essentially want to loop through each worksheet, search for todays date in column B and, if it's found, select the cell to the left of it and stop searching through the other sheets.

The dates in this column are based on formulas so I can't used find. I think I'm nearly there but I'm facing a "runtime error 91: Object variable or With block variable not set." Below is my code. I'm getting the error on the targetcell = ws.Cells(Counter, 2) line.

Sub test()

    Dim ws As Worksheet
    Dim FindString As Date               ' Todays date                  
    Dim DateRange As Range               ' range of dates on aparticular timesheet
    Dim target As Long                   ' date currently being checked
    Dim targetcell As Range              ' location of date being checked
    Dim found As Boolean                 ' checks for date found
    Dim LR As Double                     ' Last row in the column storing dates

    'convert today's date into a string that can be searched for in the date range
    FindString = CLng(Date)              

    found = False                        'starting point for found
    
    For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Dashboard" And ws.Name <> "Template" Then           'only checks timesheets
        
         ws.Activate       ' Select the timesheet         
        
         LR = ws.Range("B:B").SpecialCells(xlCellTypeLastCell).Row
    
          For Counter = 1 To LR
                targetcell = ws.Cells(Counter, 2)
                target = CLng(targetcell)
            
                If target = FindString Then
                  ws.Cells(Counter, 1).Select
                  found = True
                  Exit For
               End If
           
          Next Counter
    
          If found = True Then
            Exit For
          End If
    End If
    
    Next ws

End Sub

I have tried to get rid of targetcell = ws.Cells(Counter, 2) all together by changing target = CLng(targetcell) to target = CLng(ws.Cells(Counter, 2)) but then I get runtime error 13: type mismatch on that line.

Any help would be greatly appriciated!

Trevyn Woolf
  • 1
  • When assigning a value to an object you must use Set. i.e. Set targetcell = ws.cells(Counter,2). This is something you would be aware of if you had worked through a VBA tutorial. To avoid similar gotcha's in the future I'd recommend installing the free and fantastic Rubberduck addin for VBA and paying close attention to the Code Inspections. – freeflow Apr 14 '23 at 09:40
  • Thank you for the advice @freeflow , as I said, I'm new to VBA and have already spent hours trying to find an existing tutorial for the code I was writing so I appreciate any help. I was unaware of Rubberduck and I'm sure it would be very helpful for me, just unfortunate I am a mac user (I know, terrible) so cannot make use of it! – Trevyn Woolf Apr 14 '23 at 11:22

1 Answers1

0

You have to use Set when assigning an object to an object variable

So change it to

Set targetcell = ws.Cells(Counter, 2)

It's a common mistake when filling several variables in a row :p

Notus_Panda
  • 1,402
  • 1
  • 3
  • 12
  • Have you tried giving "target" a different name? Target is used by VBE for when an event is triggered on a certain object, i.e. `Worksheet_Changed`. Apart from that, I tested with the "target" as is and it worked fine... as long as the value is something that can be converted to a long, i.e. not text. Are you certain the values you're looping through are not text? (formatted as text doesn't matter) – Notus_Panda Apr 14 '23 at 11:50