VBA basic question

  • Thread starter Thread starter kirkm
  • Start date Start date
K

kirkm

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
 
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
 
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
 
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
 
With Worksheets("Stats").Range(p$)
For Each c In .Range
Click to expand...

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
 
Back
Top