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

  • Thread starter Thread starter Janis
  • Start date Start date
J

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
 
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)
 
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
Chip Pearson said:
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 said:
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
Click to expand...
Click to expand...
 
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
Click to expand...
 
I think Chip misspoke, er, mistyped. It works ok for me.

Chip said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top