Set Range with Variable

[Guest]

Hello, I'm out of ideas, so I decided to post here... can someone please
tell me why this isn't working... I'm *trying* to populate a combo-box with
a list of "Agency Names" from a spreadsheet... here's my code. Thanks in
advance for all of your help!!

-Allen



Private Sub UserForm_Initialize()

Dim a As Integer
Dim cell As Range
Dim Rng As Range

a = 4

' I am trying to find where the "Agency Name" list begins.

Do While Worksheets(mEvent).Cells(a, 1).Value <> "Agency Name"

a = a + 1

Loop

' Once I find the list, I add one to the row value "a" so that "Agency Name
is not included in the list.

a = a + 1

' This is the code that populates the combo-box.

With Workbooks("Doctor & Agency Marketing DB.xls").Worksheets(mEvent)

' HERE IS WHERE I KEEP GETTING THE ERROR.

Set Rng = .Range(Cells(a, 1), .Range(Cells(a, 1)).End(xlDown))

End With

For Each cell In Rng.Cells
Me.cbAgency.AddItem cell.Value
Next cell

btnOK.Enabled = False

End Sub

 

[Zone]

Set Rng = .Range(.Cells(a, 1), .Cells(a, 1).End(xlDown))

 

[Chip Pearson]

Allen,

The line of code

Set Rng = .Range(Cells(a, 1), .Range(Cells(a, 1)).End(xlDown))

should be

Set Rng = .Range(.Cells(a, 1), .Range(.Cells(a, 1)).End(xlDown))

Note the periods before the word "Cell" in both occurrences.

Without the periods, "Cells" refers to the ActiveSheet, NOT the worksheet
that you are using in your With statement. Thus, if the ActiveSheet is not
the same as the With worksheet, you'll get an error because you are
attempting to defined a Range on the With sheet using a Cells property that
refers to a different sheet. That is forbidden.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

Hey guys, thank you VERY much for your help. Unfortunately though, even with
the periods, I'm still getting an error. (probably something else I'm
overlooking...)


The error says:

Run-time error '1004':
Application-defined or object-defined error
End Debug Help


When I step-through the code one line at a time, it works fine up until I
get to that same line (even with the periods).

 

[Guest]

I decided to take another approach... and this one worked. It may not be
the cleanest way to do it, but it works. :shrug:


Private Sub UserForm_Initialize()

Dim a As Long
Dim b As Long
Dim cell As Range
Dim Rng As Range

a = 4

Do While Worksheets(mEvent).Cells(a, 1).Value <> "Agency Name"
a = a + 1
Loop
a = a + 1
b = a

Do While Worksheets(mEvent).Cells(b, 1).Value <> ""
b = b + 1
Loop
b = b - 1

With Workbooks("Doctor & Agency Marketing DB.xls").Worksheets(mEvent)
Set Rng = .Range(.Cells(a, 1), .Cells(b, 1))
End With

For Each cell In Rng.Cells
Me.cbAgency.AddItem cell.Value
Next cell

btnOK.Enabled = False

End Sub