Wrong Number of Arguments or Invalid Property Assignment???

  • Thread starter Thread starter tbassngal
  • Start date Start date
T

tbassngal

Hi everyone! When I have just two cells listed to .clear, this works.
When I add all the cells I need to be cleared, I get the Compile Error
"Wrong Number of Arguments or Invalid Property Assignment" Should I be
using something else other than Cells???

Sub RemoveStuff()
Dim i As Long
i = 2
While Not IsEmpty(Cells(i, 4))
If Cells(i, 4).Value = Cells(i - 1, 4).Value And Cells(i, 8) <=
0 Then
Range(Cells(i, 19), Cells(i, 20), Cells(i, 21), Cells(i,
22), Cells(i, 23), Cells(i, 24), Cells(i, 25), Cells(i, 26), Cells(i,
27), Cells(i, 28), Cells(i, 29), Cells(i, 30), Cells(i, 31), Cells(i,
32), Cells(i, 33), Cells(i, 34), Cells(i, 35), Cells(i, 36), Cells(i,
37), Cells(i, 38)).Clear
ElseIf Cells(i, 4).Value = Cells(i + 1, 4).Value And Cells(i,
8) <= 0 Then _
Range(Cells(i, 19), Cells(i, 20), Cells(i, 21), Cells(i,
22), Cells(i, 23), Cells(i, 24), Cells(i, 25), Cells(i, 26), Cells(i,
27), Cells(i, 28), Cells(i, 29), Cells(i, 30), Cells(i, 31), Cells(i,
32), Cells(i, 33), Cells(i, 34), Cells(i, 35), Cells(i, 36), Cells(i,
37), Cells(i, 38)).Clear
End If
i = i + 1
Wend

End Sub
 
Cells(i, 19).resize(20).Clear

is a lot easier.

--
Jim
| Hi everyone! When I have just two cells listed to .clear, this works.
| When I add all the cells I need to be cleared, I get the Compile Error
| "Wrong Number of Arguments or Invalid Property Assignment" Should I be
| using something else other than Cells???
|
| Sub RemoveStuff()
| Dim i As Long
| i = 2
| While Not IsEmpty(Cells(i, 4))
| If Cells(i, 4).Value = Cells(i - 1, 4).Value And Cells(i, 8) <=
| 0 Then
| Range(Cells(i, 19), Cells(i, 20), Cells(i, 21), Cells(i,
| 22), Cells(i, 23), Cells(i, 24), Cells(i, 25), Cells(i, 26), Cells(i,
| 27), Cells(i, 28), Cells(i, 29), Cells(i, 30), Cells(i, 31), Cells(i,
| 32), Cells(i, 33), Cells(i, 34), Cells(i, 35), Cells(i, 36), Cells(i,
| 37), Cells(i, 38)).Clear
| ElseIf Cells(i, 4).Value = Cells(i + 1, 4).Value And Cells(i,
| 8) <= 0 Then _
| Range(Cells(i, 19), Cells(i, 20), Cells(i, 21), Cells(i,
| 22), Cells(i, 23), Cells(i, 24), Cells(i, 25), Cells(i, 26), Cells(i,
| 27), Cells(i, 28), Cells(i, 29), Cells(i, 30), Cells(i, 31), Cells(i,
| 32), Cells(i, 33), Cells(i, 34), Cells(i, 35), Cells(i, 36), Cells(i,
| 37), Cells(i, 38)).Clear
| End If
| i = i + 1
| Wend
|
| End Sub
|
 
So would it be like:

Cells(i,
19).resize(20).resize(21).resize(22).resize(23).resize(24).Clear

???

This doesn't make sense to me... I've never used this before.
 
Just replace your entire .Clear statement with mine. And check out Resize
in Help.

--
Jim
| So would it be like:
|
| Cells(i,
| 19).resize(20).resize(21).resize(22).resize(23).resize(24).Clear
|
| ???
|
| This doesn't make sense to me... I've never used this before.
|
 
Back
Top