200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hi
I need fixing my problem when try running userform shows error
Method range of object worksheet failed
a = sh.Range("A2:E" & sh.Range("A" & Rows.Count).End(xlUp)).Valuethe code should show data based on sheet with add auto number in first column in listbox. I 'm not sure if auto numbers in first column in listbox causes this problem
thanks guys
Tubrak
Your code is incomplete (and makes no sense so far) but you missed the .Row end when defining the range. It should read (addition in bold):
Private Sub UserForm_Initialize()
Dim LastRow As Long
Set sh = Sheets("ATS")
a = sh.Range("A2:E" & sh.Range("A" & Rows.Count).End(xlUp).Row).Value
With MY_List
.ColumnCount = 6
.ColumnWidths = "20;50;100;100;70;70;70"
End With
End SubHope this helps.
@Tubrak,
Another thing (error) I noticed is it seems you are wanting to assign a range object to " a ". If so, you should declare " a " as a Range and not a Variant.
You have declared a variable "LastRow" but have not given it a value. Add this line:
LastRow = Range("A" & Rows.Count).End(xlUp).RowPrivate Sub UserForm_Initialize()
Dim LastRow As Long
Set sh = Sheets("ATS")
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set a = sh.Range("A2:E" & LastRow)
With MY_List
' 6 columns but 7 widths ??
.ColumnCount = 6
.ColumnWidths = "20;50;100;100;70;70;70"
End With
End SubHope this helps. If this solves your problem, please mark my answer as selected.
Cheers :-)
For i = 1 To UBound(a, 1)