# Solved: Run Time Error 91 - Object variable or With block variable not set



## mamdani99 (Oct 21, 2008)

Hi All,

Excel VBA 
Error : Run Time Error 91 - Object variable or With block variable not set

I have a userform with some textboxes and Command Buttons. When I add
the record, I receive an error message. The Code I wrote on Command Button to save the data into worksheet is as follow:


```
Private Sub cmdAddScore_Click()

Dim LastRow As Object
Dim LastRow1 As Object
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim RESPONSE As Double

Set ws = Worksheets("Matches")
Set ws1 = Worksheets("Schedule")
Set LastRow = ws.Range("A65536").End(xlUp)
Set LastRow1 = ws1.Range("H65536").End(xlUp)

RESPONSE = MsgBox("Do you want to save this record?", vbYesNoCancel)
        
 If RESPONSE = vbYes Then
        
        LastRow.Offset(1, 0).Value = txtMatchNumber.Text 'Column A
        LastRow.Offset(1, 1).Value = Format(txtMatchDate.Text, "dd.mmm.yy") 'Column B
        LastRow.Offset(1, 2).Value = Format(txtMaxOvers.Value, "00.0") 'Column C
                
        If optBattingFirst.Value = True And txtTeam1Name.Top = 150 Then
            LastRow.Offset(1, 3).Value = "BF" 'Column D
        ElseIf optBattingFirst.Value = False And txtTeam1Name.Top = 180 Then
            LastRow.Offset(1, 9).Value = "BS" 'Column J
        End If
                
        If txtTeam1Runs.Top = 150 Then
            LastRow.Offset(1, 4).Value = txtTeam1Name.Text 'Column E
            LastRow.Offset(1, 5).Value = txtTeam1Runs.Value 'Column F
            LastRow.Offset(1, 6).Value = txtTeam1Wkts.Value 'Column G
            LastRow.Offset(1, 7).Value = Format(txtTeam1Overs.Value, "00.0") 'Column H
            LastRow.Offset(1, 8).Value = txtSOTeam1.Text 'Column I
        Else
            LastRow.Offset(1, 10).Value = txtTeam1Name.Text 'Column K
            LastRow.Offset(1, 11).Value = txtTeam1Runs.Value 'Column L
            LastRow.Offset(1, 12).Value = txtTeam1Wkts.Value 'Column M
            LastRow.Offset(1, 13).Value = Format(txtTeam1Overs.Value, "00.0") 'Column N
            LastRow.Offset(1, 14).Value = txtSOTeam1.Text 'Column O
            LastRow1.Offset(1, 0).Value = "Yes" 'new line
        End If
            
        If optBattingSecond.Value = True And txtTeam2Name.Top = 150 Then
            LastRow.Offset(1, 3).Value = "BF" ''Column D
        ElseIf optBattingSecond.Value = False And txtTeam2Name.Top = 180 Then
            LastRow.Offset(1, 9).Value = "BS" 'Column J
        End If
                
        If txtTeam2Runs.Top = 150 Then
            LastRow.Offset(1, 4).Value = txtTeam2Name.Value 'Column E
            LastRow.Offset(1, 5).Value = txtTeam2Runs.Value 'Column F
            LastRow.Offset(1, 6).Value = txtTeam2Wkts.Value 'Column G
            LastRow.Offset(1, 7).Value = Format(txtTeam2Overs.Value, "00.0") 'Column H
            LastRow.Offset(1, 8).Value = txtSOTeam2.Text 'Column I
        Else
            LastRow.Offset(1, 10).Value = txtTeam2Name.Value 'Column K
            LastRow.Offset(1, 11).Value = txtTeam2Runs.Value 'Column L
            LastRow.Offset(1, 12).Value = txtTeam2Wkts.Value 'Column M
            LastRow.Offset(1, 13).Value = Format(txtTeam2Overs.Value, "00.0") 'Column N
            LastRow.Offset(1, 14).Value = txtSOTeam2.Text 'Column O
        LastRow1.Offset(1, 0).Value = "Yes" 'new line
        End If
       
        ElseIf RESPONSE = vbCancel Then
            Exit Sub
        Else
    
    End If
         
        With frmMatches.ListBox1
            If Range("H2") = "" Then
                .RowSource = "Schedule!A2" & ":G" & xlLastRow
            ElseIf Range("H" & xlLastRow) = "Yes" Then
                MsgBox "All data entered"
                cmdAddScore.Enabled = True
                Unload Me
                Unload frmMatches
                Exit Sub
            Else
                .RowSource = "Schedule!A" & Columns(8).Find("Yes", _
                searchdirection:=xlPrevious).Row + 1 & ":G" & xlLastRow("Schedule")
            End If
         End With
         
        MsgBox frmMatches.ListBox1.ListCount

        If frmMatches.ListBox1.ListCount = 1 Then
               frmMatches.Label1.Caption = frmMatches.ListBox1.ListCount & " Record found"
        Else
               frmMatches.Label1.Caption = frmMatches.ListBox1.ListCount & " Records found"
        End If
        
        Unload Me
End Sub
```
I received the error message on this part of my code:

```
.RowSource = "Schedule!A" & Columns(8).Find("Yes", _
                searchdirection:=xlPrevious).Row + 1 & ":G" & xlLastRow("Schedule")
```
Can anyone solve it?

Thanks in advance

Mamdani


----------



## Rollin_Again (Sep 4, 2003)

```
.RowSource = "Schedule!A" & Columns(8).Find("Yes", _
                searchdirection:=xlPrevious).Row + 1 & ":G" & [B]xlLastRow[/B]("Schedule")
```
Where is your *xlLastRow* function defined?

Regards,
Rollin


----------



## mamdani99 (Oct 21, 2008)

Thanks for your response, Mr. Rollin_Again.

Here is the code of *xlLastRow Function*.


```
Function xlLastRow(Optional WorksheetName As String) As Long
 
    'Check for optional worksheetname else use activesheet
    If WorksheetName = vbNullString Then
        WorksheetName = ActiveSheet.Name
    End If
 
     '    find the last populated row in a worksheet
    With Worksheets(WorksheetName)
        xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _
        xlWhole, xlByRows, xlPrevious).Row
    End With
 
End Function
```


----------



## Rollin_Again (Sep 4, 2003)

I'm tried several times and am unable to duplicate the error. Could you post your sample workbook or email to me so I can try to debug it?

Regards,
Rollin


----------



## mamdani99 (Oct 21, 2008)

Thank you once again, Rollin_Again

Yes off course. I am attaching a excel file for you. Plz open "*Score Board*". I have a bug in this form.

Thanks in advance.


----------



## Rollin_Again (Sep 4, 2003)

The "Matches" worksheet is the active sheet when the error is thrown and your code is trying to FIND a value of "YES" in column 8 of this sheet. Since column 8 of the "Matches" worksheet contains only numerical values it will never find a match on your search criteria. You need to add the correct sheet name to search to your FIND statement.


```
.RowSource = "Schedule!A" & [B][COLOR="Red"]Sheets("Schedule").[/COLOR][/B]Columns(8).Find("Yes", _
searchdirection:=xlPrevious).Row + 1 & ":G" & xlLastRow("Schedule")
```
Regards,
Rollin


----------



## mamdani99 (Oct 21, 2008)

Thank you so much for your responses. Mr. Rollin_Again. Now this problem has been solved but One thing more I want to sovle. This form has still one bug. 

```
With frmMatches.ListBox1
            If Range("H2") = "" Then
                .RowSource = "Schedule!A2" & ":G" & xlLastRow
            ElseIf Range("H" & xlLastRow) = "Yes" Then
                MsgBox "All data entered"
                cmdAddScore.Enabled = True
                Unload Me
                Unload frmMatches
                Exit Sub
            Else
                .RowSource = "Schedule!A" & Sheets("Schedule").Columns(8).Find("Yes", _
                searchdirection:=xlPrevious).Row + 1 & ":G" & xlLastRow("Schedule")
            End If
         End With
 
I want this part of code error free.
```
 ElseIf Range("H" & xlLastRow) = "Yes" Then
MsgBox "All data entered"
cmdAddScore.Enabled = True
Unload Me
Unload frmMatches
Exit Sub
[/CODE]

I hope you will response very soon


----------



## Rollin_Again (Sep 4, 2003)

What is the specific error you are receiving and which line of code is causing it?

Regards,
Rollin


----------



## mamdani99 (Oct 21, 2008)

Dear

The problem is on last record. See the attached zip file
Form : Score Board

Plz open the score board form and enter the data. When you open the form, you will find only one and last record in listbox. then click on Add Score button. and put the data in six text boxes. When you click the add button you will be asked to save the existing data. Click on Save button.
after saving the record the listbox must be blank becouse all the data of Schedule worksheet has been entered but th lisbox shows two records. This is the point that I want to solve.

Plz help me.


----------



## Rollin_Again (Sep 4, 2003)

When you call the *xlLastRow* function from your macro you need to pass the sheetname ("Schedule") as an argument to the function unless it is the activesheet. There are several places in your code where this needs to be done since the "Matches" sheet is the active sheet when the code is executed. You also need to specify the worksheet name when evaluating each range. I have highlighted the code in red below which needs to be added.


```
With frmMatches.ListBox1
If [COLOR="Red"]Sheets("Schedule")[/COLOR].Range("H2") = "" Then
.RowSource = "Schedule!A2" & ":G" & xlLastRow[COLOR="red"]("Schedule")[/COLOR]
ElseIf [COLOR="red"]Sheets("Schedule")[/COLOR].Range("H" & xlLastRow[COLOR="red"]("Schedule")[/COLOR]) = "Yes" Then
MsgBox "All data entered"
cmdAddScore.Enabled = True
Unload Me
Unload frmMatches
Exit Sub
Else
RowSource = "Schedule!A" & Sheets("Schedule").Columns(8).Find("Yes", _
searchdirection:=xlPrevious).Row + 1 & ":G" & xlLastRow("Schedule")
End If
End With
```
Regards,
Rollin


----------



## mamdani99 (Oct 21, 2008)

Thank you so much for your interest, corporation & support. Dear sometimes we forget only one line of code and it seems to be very difficult to find it out. I only write this code at the start of with block and it has solved all my entire problems and this thread has been resolved.


```
sheets("Schedule").select
```
Now I will start the Edit/Modify the "Matches" Record. If I need you I will ask you for help. I hope you will help me like this.

Thanks & best Regards


----------



## Rollin_Again (Sep 4, 2003)

Glad to help! I'll be here if you need me so please start a new thread if you have additional problems.

Regards,
Rollin


----------

