1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by mamdani99, May 22, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. mamdani99

    mamdani99 Thread Starter

    Joined:
    Oct 21, 2008
    Messages:
    15
    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:

    Code:
    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:
    Code:
                    .RowSource = "Schedule!A" & Columns(8).Find("Yes", _
                    searchdirection:=xlPrevious).Row + 1 & ":G" & xlLastRow("Schedule")
    
    Can anyone solve it?

    Thanks in advance

    Mamdani
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Code:
     .RowSource = "Schedule!A" & Columns(8).Find("Yes", _
                    searchdirection:=xlPrevious).Row + 1 & ":G" & [B]xlLastRow[/B]("Schedule")
    

    Where is your xlLastRow function defined?

    Regards,
    Rollin
     
  3. mamdani99

    mamdani99 Thread Starter

    Joined:
    Oct 21, 2008
    Messages:
    15
    Thanks for your response, Mr. Rollin_Again.

    Here is the code of xlLastRow Function.

    Code:
    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
    
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    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
     
  5. mamdani99

    mamdani99 Thread Starter

    Joined:
    Oct 21, 2008
    Messages:
    15
    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.
     

    Attached Files:

  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    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.

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

    mamdani99 Thread Starter

    Joined:
    Oct 21, 2008
    Messages:
    15
    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.
    Code:
    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
     
  8. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    What is the specific error you are receiving and which line of code is causing it?

    Regards,
    Rollin
     
  9. mamdani99

    mamdani99 Thread Starter

    Joined:
    Oct 21, 2008
    Messages:
    15
    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.
     

    Attached Files:

  10. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    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.

    Code:
    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
     
  11. mamdani99

    mamdani99 Thread Starter

    Joined:
    Oct 21, 2008
    Messages:
    15
    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.

    Code:
     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
     
  12. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Glad to help! I'll be here if you need me so please start a new thread if you have additional problems.

    Regards,
    Rollin
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/829238

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice