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 2 Answers
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")
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
