VBA basic question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kirkm - 31 Dec 2006 06:29 GMT

I should know why this doesn't work...but
get 'Wrong number of arguments or invalid property assignment'
and can't resolve it.

With Worksheets("Stats").Range(p$)

For Each c In .Range
Debug.Print c.Value
Next

End With

The value of p$ is "C3:C20"

Many thanks - Kirk

Reply to this Message

Nigel - 31 Dec 2006 07:58 GMT

You cannot name a range p$ try this.....

Dim p As Range, c As Range
Set p = Range("C3:C20")

With Worksheets("Stats")

For Each c In p
Debug.Print c.Value
Next

End With

Signature

Cheers
Nigel

>I should know why this doesn't work...but
> get 'Wrong number of arguments or invalid property assignment'
[quoted text clipped - 11 lines]
>
> Many thanks - Kirk

Reply to this Message

SongBear - 31 Dec 2006 08:21 GMT

kirkm, You are doing the range thing twice; this will work:

Sub MacroTaDa()
Dim p$
p$ = "C3:C20"

With Worksheets("Stats")
For Each c In .Range(p$)
Debug.Print c.Value
Next
End With

End Sub

> I should know why this doesn't work...but
> get 'Wrong number of arguments or invalid property assignment'
[quoted text clipped - 11 lines]
>
> Many thanks - Kirk

Reply to this Message

SongBear - 31 Dec 2006 08:28 GMT

Or this will work:
Sub MacroTaDa2()
Dim p$
p$ = "C3:C20"

For Each c In Worksheets("Stats").Range(p$)
Debug.Print c.Value
Next

End Sub

> I should know why this doesn't work...but
> get 'Wrong number of arguments or invalid property assignment'
[quoted text clipped - 11 lines]
>
> Many thanks - Kirk

Reply to this Message

Dana DeLouis - 31 Dec 2006 13:42 GMT

> With Worksheets("Stats").Range(p$)
> For Each c In .Range

Hi. You have answers from others.
Just to point out, your method would work if you drilled one level down ie.
"Cells" instead of "Range."

Sub Demo()
Dim P$
Dim C As Range
P$ = "C3:C20"

With Worksheets("Stats").Range(P$)
For Each C In .Cells
Debug.Print C.Value
Next
End With
End Sub

Signature

HTH :>)
Dana DeLouis
Windows XP & Office 2003

>I should know why this doesn't work...but
> get 'Wrong number of arguments or invalid property assignment'
[quoted text clipped - 11 lines]
>
> Many thanks - Kirk

Reply to this Message

JMB - 31 Dec 2006 17:39 GMT

Please don't multipost.

http://www.cpearson.com/excel/newposte.htm

> I should know why this doesn't work...but
> get 'Wrong number of arguments or invalid property assignment'
[quoted text clipped - 11 lines]
>
> Many thanks - Kirk

Reply to this Message