VBA reference issue

Question

Im at a loss here, why do i get run-time error 91: "object variable or with block variable not set" What am i doing wrong here and what's the fix?

Thank you in advance!


Set StartCell = Worksheets("Data").Range("A5")
Set StartSheet = Worksheets("Data")

With Worksheets("Data").Range("A4:BZ4")
    Set LastColumn = .Find("Comment", LookIn:=xlValues)
Debug.Print StartCell.Row
Debug.Print StartCell.Column
Set Workrange = Range(StartSheet.Cells(StartCell.Row, StartCell.Column), StartSheet.Cells(5000, LastColumn.Column)) 'This line is the issue
End With


If Not Intersect(Target, Workrange) Is Nothing Then
If Target.Count > 1 Then Exit Sub
StartSheet.Cells(Target.Row, LastColumn.Column + 1).Value = Environ("username")
StartSheet.Cells(Target.Row, LastColumn.Column + 2).Value = Format(Now, "dd/mm/yyyy_hh.mm.ss")
End If


End Sub

You're assuming that the `Find` succeeded. It didn't, so `LastColumn` is `Nothing`. — BigBen, Aug 26 '20 at 17:33
```LastColumn``` is probably not set because ```.find``` didn't return anything. As an aside; in the row with the error the outer ```range``` should have a sheet reference and you can shorten ```StartSheet.Cells(StartCell.Row, StartCell.Column)``` to ```StartCell``` — Warcupine, Aug 26 '20 at 17:34
I would still recommend to check you declarations. I removed my previous question "answer". — Michal, Aug 26 '20 at 18:57
If this code is part of a worksheet event handler then you don't need to reference the sheet explicitly - it's already the default in a worksheet module... Or you can use `Me` if you want to be explicit. — Tim Williams, Aug 26 '20 at 18:58
Since you are working with `Worksheet_Change`, if possible do go through [THIS](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) — Siddharth Rout, Aug 27 '20 at 06:02

score 0 · Answer 1 · answered Aug 26 '20 at 22:46

Issues in your code

Find might not return a hit, test for that
Find uses the last set values for several parameters. Not specifying them can give unexpected results.

Other improvements

If you are going to exit on Target.Count > 1 then just do it
In a Worrksheet code behind module, use Me to refer to that sheet
Dim your variables
Removed redundant With
Cleaned up cumbersome range reference

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim StartCell As Range
    Dim LastColumn As Range
    Dim Workrange As Range

    If Target.Count > 1 Then Exit Sub

    Set StartCell = Me.Range("A5")
    'Debug.Print StartCell.Row
    'Debug.Print StartCell.Column

    Set LastColumn = Me.Range("A4:BZ4").Find( _
      What:="Comment", _
      LookIn:=xlValues, _
      LookAt:=xlWhole, _
      SearchOrder:=xlByRows, _
      MatchCase:=False, _
      MatchByte:=TRUE) ' only need MatchByte if you have selected or installed double-byte language support.

    If Not LastColumn Is Nothing Then
        Set Workrange = Me.Range(StartCell, Me.Cells(5000, LastColumn.Column)) 

        If Not Intersect(Target, Workrange) Is Nothing Then
            Me.Cells(Target.Row, LastColumn.Column + 1).Value = Environ("username")
            Me.Cells(Target.Row, LastColumn.Column + 2).Value = Format$(Now, "dd/mm/yyyy_hh.mm.ss")
        End If
    End If
End Sub

VBA reference issue

1 Answers1