CODEWITHSUNDEEP
vbaexcelrange
Luis Filho
Luis Filho

Reputation: 1

VBA rejecting empty Range of SpecialCells

I'm having a problem with a "For Each" related to a Range defined by a SpecialCells method. When I run the code below, there is a "Next without For" error, which I believe is because the rRange is empty when I first run the code. I could put "On Error Resume Next" in the beggining of the sub, but I'm trying to avoid this.

Public Sub Sub1()

Set rRange = Worksheets("Combate").Range("69:99").SpecialCells(xlCellTypeConstants, xlNumbers)
If Not rRange Is Nothing Then
    For Each c In rRange
        If c.Value <= turnoseg Then
            c.Offset(-2 * lincomb0 + 6).Value = c.Offset(-lincomb0 + 3).Value
            c.Value = ""
        Next c
    atualizarefeitos6
    End If

End Sub

In another sub, I'm having a "No cells were selected" error after I run the code below. I really don't know how to actually solve the errors in these subs, but you guys surely would know.

Sub efeitosaddatac6()

'On Error Resume Next
Set rRange = Worksheets("Combate").Range("69:99").SpecialCells(xlCellTypeConstants, xlNumbers)
For Each c In rRange
    c.Value = c.Value + 1
    Next c
atualizarefeitos6

End Sub

Thanks in advance.

Upvotes: 0

Views: 1085

Answers (2)

Vasant Kumbhojkar
Vasant Kumbhojkar

Reputation: 61

Luis Filho, You need to insert:

     End If

before

     Next c

Another item you need to define is:

     atualizarefeitos6

Is this a variable or function?

Upvotes: 0

YowE3K
YowE3K

Reputation: 23974

As pointed out in a comment by John Coleman, your first subroutine isn't working because you are missing an End If. You probably want:

Public Sub Sub1()    
    Set rRange = Worksheets("Combate").Range("69:99").SpecialCells(xlCellTypeConstants, xlNumbers)
    If Not rRange Is Nothing Then
        For Each c In rRange
            If c.Value <= turnoseg Then
                c.Offset(-2 * lincomb0 + 6).Value = c.Offset(-lincomb0 + 3).Value
                c.Value = ""
            End If
        Next c
        atualizarefeitos6
    End If
End Sub

This is one of the reasons that consistent indentation of code is useful - it highlights missing End Ifs, etc.

I would recommend you change your second subroutine as follows:

Sub efeitosaddatac6()        

    Set rRange = Nothing
    On Error Resume Next
    Set rRange = Worksheets("Combate").Range("69:99").SpecialCells(xlCellTypeConstants, xlNumbers)
    On Error GoTo 0

    If Not rRange Is Nothing Then        
        For Each c In rRange
            c.Value = c.Value + 1
        Next c
        atualizarefeitos6
    End If    
End Sub

Also, if you are not already using Option Explicit at the start of your code module, I recommend you do so. (I'm hoping that you are already using it, and that the lack of variable declarations within each subroutine is simply because they have all been declared at the module level.)

Upvotes: 1

Related Questions