0

Within a User Form, I have the following VBA code written to populate a listbox of items (Column C) out on loan based on a combo box value (more specifically the person's name - Column B) from a loans spreadsheet. However, it does not run correctly - It executed correctly the first time I ran it, and then it did not. I have figured out that it only works when I Run the code (through VBA window) with the "Loans" sheet/tab on screen. How can I ensure this code works correctly when I am on the main user sheet/tab?

Any help would be appreciated!


    Me.ListItem.Clear
       With Worksheets("Loans").Range("B1:A" & (Cells(65536, 1).End(xlUp).Row))
            Set c = .Find(cboName.Value, LookIn:=xlValues, Lookat:=xlWhole)
            If Not c Is Nothing Then
            firstAddress = c.Address
        Do
            Set c = .FindNext(c)
            ListItem.AddItem .Cells(c.Row, 3).Value
        Loop While Not c Is Nothing And c.Address <> firstAddress
            End If
        End With
End Sub
jtm
  • 35
  • 7
  • 2
    Becuase `Cells(65536, 1).End(xlUp).Row` is not fully qualified. Use `.` before Cells. or better, find the last row in a better manner as shown [HERE](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) – Siddharth Rout Jul 25 '19 at 05:52
  • If you search SO, this problem has been mentioned and covered in so many posts :) – Siddharth Rout Jul 25 '19 at 05:53
  • https://stackoverflow.com/questions/19855666/error-in-selecting-range-to-copy – Siddharth Rout Jul 25 '19 at 06:04
  • Brilliant, thanks @SiddharthRout I have fixed it and updated it to the more reliable last row method. – jtm Jul 25 '19 at 06:49

0 Answers0