Search code examples
excelvbauserformerror-code

Why am I getting a "Run-time error '1004'" when I use this code on one worksheet, but not another?

First off, here are my two codes:

Private Sub UserForm_Initialize()

Dim ws As Worksheet
Dim rng As Range
Dim LRow As Long
Dim Myarray As Variant

 Set ws = ThisWorkbook.Sheets(Mark1.LEADLISTDROPDOWN.Value)
 
 With ws
 LRow = .Range("BJ" & .Rows.Count).End(xlUp).Row
 
 Set rng = ws.Range("A:KH" & LRow)
 
 PopoutLeadListBox.RowSource = rng.Address(External:=True)
 
 End With
 
End Sub

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim rng As Range
Dim LRow As Long
Dim Myarray As Variant

 Set ws = ThisWorkbook.Sheets(SalesForm.BHSDLEADLISTSCOMBOBOXLF.Value)
 
 With ws
 LRow = .Range("S" & .Rows.Count).End(xlUp).Row
 
 Set rng = ws.Range("H2:Y" & LRow)
 
 PopoutLeadListBox.RowSource = rng.Address(External:=True)
 
 End With
 
End Sub

The first one is popping up the error code, saying Method Range of object worksheet has failed. The second works just fine. To my knowledge, the only things that differ are the size of the ranges and the objects selected.

I wrote this a while ago with help online, and I am having trouble retracing my steps. Any ideas?

Solution

  • You have a mistyping in this line

    Set rng = ws.Range("A:KH" & LRow)

    Define the Range in this way Range("Ax:KH" & LRow)

    where x is a number of the row of the range's top left cell.