1

I keep getting a run-time error of 91 which I searched up and it seems as if it's an issue of whether or not I set/declared my variables/objects properly. I'm pretty sure I referenced everything properly, and the same macro was working earlier... But then all of a sudden the vba macro broke. Any clues as to why this is the case?

Note: There are 2 separate workbooks. 1 workbook acts as a database for clients, and one workbook is a templated sheet where I can search for a client, and it autopopulates the templated sheet.

The error is found when assigning the variable LastRow with a value. I tried using Set in front, but it still didn't seem to work.

Sub AddNewClient()
    Application.ScreenUpdating = False
    Dim i As Long
    Dim LastRow As Long
    Dim strClient As String
    Dim strEmail As String
    Dim strPhone As String
    Dim ClientDBWB As Excel.Workbook
    
    Set CurrWkbk = ActiveWorkbook
       
    ClientDBWB = Workbooks.Open("[SomePath][File Name.xlsx]")

***The Debugger shows the error here*** when assigning LastRow a value.
 
    LastRow = ClientDBWB.Sheets("ClientDB").Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    strClient = CurrWkbk.Sheets("House").Range("C11:J11").Cells(1, 1).Value
    strEmail = CurrWkbk.Sheets("House").Range("C12:J12").Cells(1, 1).Value
    strPhone = CurrWkbk.Sheets("House").Range("C13:J13").Cells(1, 1).Value
    
    With ClientDBWB.Sheets("ClientDB")
        .Cells(LastRow, 1).Value = strClient
        .Cells(LastRow, 2).Value = strPhone
        .Cells(LastRow, 4).Value = strEmail
    End With
    MsgBox "Client is Added to the Database!"
    Application.ScreenUpdating = True
    
6
  • Maybe there is no such worksheet? Or the .Open command failed, and ClientDBWB is undefined?
    – Aganju
    May 26, 2021 at 20:27
  • Which line of code is showing the error? May 26, 2021 at 20:35
  • The debugger highlights "LastRow = ClientDBWB.Sheets("ClientDB").Cells(Rows.Count, "A").End(xlUp).Row + 1" as the error. @Reddy I made sure the path names were correct and I did a check to see if ClientDBWB returned something, and it did. May 26, 2021 at 20:36
  • Edit: It started working again, but I didn't really change anything??? May 26, 2021 at 20:45
  • You can check the object before attempting to use it: If ClientDBWB Is Nothing.
    – Yorik
    May 26, 2021 at 20:52

2 Answers 2

0

The error is found when assigning the variable LastRow with a value. I tried using Set in front, but it still didn't seem to work.

If you used SET in front of LastRow then you missed the syntax of VBA. Aus you declared LastRow to be a Long, assignments can only done using the "="-operator.

On the other hand ClientDBWB is an object where you have to use SET command. Use the debugger to see the different outcome when using SET and when missing it.

Use "option explicit" to enforce you to think about what type of objects you are handling as you have to declare everything.

Another issue I saw is that:

strClient = CurrWkbk.Sheets("House").Range("C11:J11").Cells(1, 1).Value

is a more complicated way to write:

strClient = CurrWkbk.Sheets("House").Range("C11").value

or even

strClient = CurrWkbk.Sheets("House").Range("C11")

0

Copy by Assignment

Option Explicit

Sub AddNewClient()
    
    ' Source
    Dim swb As Workbook: Set swb = ThisWorkbook ' workbook containing this code
    Dim sws As Worksheet: Set sws = swb.Worksheets("House")
    
    Application.ScreenUpdating = False
    
    ' Destination
    Dim dwb As Workbook: Set dwb = Workbooks.Open("C:\Test\Test.xlsx")
    Dim dws As Worksheet: Set dws = dwb.Worksheets("ClientDB")
    Dim dRow As Long: dRow = dws.Cells(dws.Rows.Count, "A").End(xlUp).Row + 1
'    ' Or...
'    Dim dRow As Long: dRow = dws.Cells(dws.Rows.Count, 1).End(xlUp).Row + 1
'    Dim dRow As Long: dRow = dws.Range("A" & dws.Rows.Count).End(xlUp).Row + 1
    
    ' Copy
    dws.Cells(dRow, 1).Value = sws.Range("C11").Value ' Client
    dws.Cells(dRow, 2).Value = sws.Range("C12").Value ' Phone
    dws.Cells(dRow, 4).Value = sws.Range("C13").Value ' Email
    
    ' Or opting for only 'Range' or only 'Cells':
'    ' Or...
'    dws.Range("A" & dRow).Value = sws.Range("C11").Value ' Client
'    dws.Range("B" & dRow).Value = sws.Range("C12").Value ' Phone
'    dws.Range("D" & dRow).Value = sws.Range("C13").Value ' Email
'    ' Or...
'    dws.Cells(dRow, "A").Value = sws.Cells(11, "C").Value ' Client
'    dws.Cells(dRow, "B").Value = sws.Cells(12, "C").Value ' Phone
'    dws.Cells(dRow, "D").Value = sws.Range(13, "C").Value ' Email
'    ' Or...
'    dws.Cells(dRow, 1).Value = sws.Cells(11, 3).Value ' Client
'    dws.Cells(dRow, 2).Value = sws.Cells(12, 3).Value ' Phone
'    dws.Cells(dRow, 4).Value = sws.Range(13, 3).Value ' Email

    ' Save and Close
    'dwb.Close SaveChanges:=True
    
    Application.ScreenUpdating = True

    ' Inform
    MsgBox "Client is Added to the Database!"
    
End Sub

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .