CODEWITHSUNDEEP
vbaexcel
Gajju
Gajju

Reputation: 443

Assignment to constant not allowed

I am getting this error in one of my macros, the code is

Dim rdel1 As Range
Dim rdel2 As Range
Set rdel1 = Sheets("Sheet1").Range(A1:B100)
For Each rdel2 In rdel1.Cells
    If rdel2.Value = "No item selected" Then
        rdel2.Offset(1, 0).EntireRow.Delete
        rdel2.EntireRow.Delete
        rdel2.Address = rdel2.Offset(-1, 0)       "Error in this line"
    End If
Next rdel2

I want to change the address of redel2 by offset(-1,0). I know it dosen't look the right way to write it but I am unable to get the right syntax to change it. Can someone help! Please!

Upvotes: 1

Views: 4640

Answers (2)

chris neilsen
chris neilsen

Reputation: 53126

After you execute 

rdel2.EntireRow.Delete

rdel2 will be `Nothing' so any attempt to manipulate it will fail.

If it were not Nothing, and referenceing a cell in a row > 1, then 

Set rdel2 = rdel2.Offset(-1, 0)

would work.

It's not clear exactly what you want to achieve, but this may get you started

Sub Demo()
    Dim rdel1 As Range
    Dim rdel2 As Range

    Set rdel1 = Sheets("Sheet1").Range("A1:A100")
    Dim rw As Long
    For rw = rdel1.Rows.Count To 1 Step -1
        Set rdel2 = rdel1.Cells(rw, 1)
        If rdel2.Value = "No item selected" Then
            rdel2.Offset(1, 0).EntireRow.Delete
        End If
    Next
End Sub

Upvotes: 2

GôTô
GôTô

Reputation: 8053

rdel2 is a range and .Offset(-1,0) returns a range, just do rdel2 = rdel2.Offset(-1, 0) if you want to change rdel2.

Although, in your case, the For Each loop will update rdel2 so this line will be useless (unless you are not showing all your code and there is actually more between the problematic line and the Next rdel2 statement)

Upvotes: 0

Related Questions