Object Variable and With block not set error from excel vba

Question

I have the following code, but excel is giving me error 91: Object Variable or With block variable not set error

It's specifically highlighting the line where I set lr1 equal to the find formula.

I dont know why its doing that.

Pls. Help!

Here is the code:

'Start Argument
'This Argument will deal with locating the last empty row and inserting Free Rent & Recoveries Headers
Sub LER()

'The variable lr1 is set as a Long Number
Dim lr1 As Long

'The variable lc1 is set as a Long Number
Dim lc1 As Long

'The variable sr1 is set as a Long Number
Dim sr1 As Long

Sheets("Sheet1").Range("A1").Activate

'The variable lr1 is used to store the find formula to locate the last row of cells containing any data
lr1 = Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

'The variable sr1 is used to go to the truly last row of empty cells with no data whatsoever.
'This is always after the last row of cells with data, hence the "lr1 + 1" formula utilization
sr1 = lr1 + 1

'Cells from the last empty row in coloumns A through G are selected to be merged and centered
Union(Cells(sr1, 1), Cells(sr1, 2), Cells(sr1, 3), Cells(sr1, 4), Cells(sr1, 5), Cells(sr1, 6), Cells(sr1, 7)).Select

MsgBox "The last Cell is:" + sr1

'End of Argument
End Sub

Probably means it doesn't find anything so lr1 is assigned to the row of Nothing. — SJR
– SJR, Commented Jul 18, 2017 at 16:03

Scott Holtzman · Accepted Answer · 2017-07-18 15:52:02Z

2

Qualify the objects to their parent:

Dim ws1 as Worksheet
Set ws1 = Worksheets("Sheet1")

With ws1

    Dim rLastCell as Range
    Set rLastCell = .Cells.Find(What:="*", After:=.Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)

    Dim lr1 as Long
    lr1 = rLastCell.Row

    '... more code

End With

answered Jul 18, 2017 at 15:52

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Karthik Ramachandran Over a year ago

Thank you for your help! Worked great!

Collectives™ on Stack Overflow

Object Variable and With block not set error from excel vba

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related