runtime error 434, object does not support this method or property

[Janis]

it can't compile, It appears to be the ws as object declaration. How do I
declare ws?
tnx,


Private Sub UserForm_Initialize()

Dim intLastRow As Integer
Dim ws As Object
Dim c As Range
Dim rng As Range
Set ws = ThisWorkbook.Worksheets("patients")


intLastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
'intLastRow = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)



Set rng = ws.Range("A1:A" & intLastRow)
ComboBox2.Clear
For Each c In Worksheets("patients").rng
ComboBox2.AddItem c.Value
Next c

End Sub

 

[Chip Pearson]

The Cells property does not support a SpecialCells property.

intLastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
to
intLastRow = ws.SpecialCells(xlCellTypeLastCell).Row


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Janis]

Thank you Chip. I recompiled and now it goes farther wo an error except now
it stops compiling on "the for each c" ....
thanks again

The Cells property does not support a SpecialCells property.

intLastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
to
intLastRow = ws.SpecialCells(xlCellTypeLastCell).Row


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

it can't compile, It appears to be the ws as object declaration. How do I
declare ws?
tnx,


Private Sub UserForm_Initialize()

Dim intLastRow As Integer
Dim ws As Object
Dim c As Range
Dim rng As Range
Set ws = ThisWorkbook.Worksheets("patients")


intLastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
'intLastRow = Cells.Find(What:="*", After:=[A1],
SearchDirection:=xlPrevious)



Set rng = ws.Range("A1:A" & intLastRow)
ComboBox2.Clear
For Each c In Worksheets("patients").rng
ComboBox2.AddItem c.Value
Next c

End Sub

 

[Dave Peterson]

Dim ws as Worksheet
....
and
for each c in rng.cells

I'd also
dim intLastRow as long

(forget that integer exists--always use Long. forget that single exists--always
use Double.)

it can't compile, It appears to be the ws as object declaration. How do I
declare ws?
tnx,

Private Sub UserForm_Initialize()

Dim intLastRow As Integer
Dim ws As Object
Dim c As Range
Dim rng As Range
Set ws = ThisWorkbook.Worksheets("patients")

intLastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
'intLastRow = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)


Set rng = ws.Range("A1:A" & intLastRow)
ComboBox2.Clear
For Each c In Worksheets("patients").rng
ComboBox2.AddItem c.Value
Next c

End Sub

 

[Dave Peterson]

I think Chip misspoke, er, mistyped. It works ok for me.

The Cells property does not support a SpecialCells property.

intLastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
to
intLastRow = ws.SpecialCells(xlCellTypeLastCell).Row

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

it can't compile, It appears to be the ws as object declaration. How do I
declare ws?
tnx,


Private Sub UserForm_Initialize()

Dim intLastRow As Integer
Dim ws As Object
Dim c As Range
Dim rng As Range
Set ws = ThisWorkbook.Worksheets("patients")


intLastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
'intLastRow = Cells.Find(What:="*", After:=[A1],
SearchDirection:=xlPrevious)



Set rng = ws.Range("A1:A" & intLastRow)
ComboBox2.Clear
For Each c In Worksheets("patients").rng
ComboBox2.AddItem c.Value
Next c

End Sub