CODEWITHSUNDEEP
vbaexcel
SAS_N00b
SAS_N00b

Reputation: 81

How do I use Loop with Variable Range

In the following code, when I get to for each curCell in Range(i) it is not able to understand the Range that I am referencing. I receive a Method 'Range' of Object'_worksheet' failed. I know the current Range(i) is incorrect, but I have tried every variation and nothing seems to work.

Can someone please help me understand how to get my code to recognize Range1, then Range2, etc? 

Sub Worksheet_Change(ByVal Target As Range)

Dim Range1 As Range, Range2 As Range, Range3 As Range, Range4 As Range
Dim curCell As Variant
Dim i As Integer
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets(cCostingQSheet)

Set Range1 = ws.Range("E6:E9")
Set Range2 = ws.Range("E15:E19")
Set Range3 = ws.Range("E21")
Set Range4 = ws.Range("E23")

For i = 1 To 4

    For Each curCell In Range(i).Cells

        If Not WorksheetFunction.IsNumber(curCell) = True Then

            curCell.Value = 0

        Else

        End If

    Next

Next

End Sub

Upvotes: 1

Views: 81

Answers (3)

brettdj
brettdj

Reputation: 55692

I would go a step further and

  • Look at each range inside the overall range.
  • use a variant array to process the range (where the range area is more than one cell), then dump back to the range.

code

Sub recut()
    Dim rng1 As Range
    Dim rng2 As Range
    Dim lngCol As Long
    Dim lngRow As Long
    Dim ws As Worksheet

    Set ws = ActiveSheet
    Set rng1 = ws.Range("E6:E9,E15:E19,E21,E23")

    For Each rng2 In rng1.Areas
        If rng2.Cells.Count > 1 Then
            x = rng2.Value
            For lngRow = 1 To UBound(x)
                For lngCol = 1 To UBound(x, 2)
                    If IsNumeric(x(lngRow, lngCol)) Then x(lngRow, lngCol) = 0
                Next
            Next
            rng2.Value2 = x
        Else
            If IsNumeric(rng2.Value) Then rng2.Value = 0
        End If
    Next rng2

End Sub

Upvotes: 1

John Coleman
John Coleman

Reputation: 52008

You could assign an non-contiguous range to a range variable instead of using either 4 different range variables or an array of ranges (as @tigeravatar suggested in their excellent answer). Something like this:

Sub test()
    Dim R As Range
    Dim myCell As Range
    Dim ws As Worksheet
    Dim i As Long

    Set ws = ActiveSheet
    Set R = ws.Range("E6:E9,E15:E19,E21,E23")
    i = 1
    For Each myCell In R.Cells
        myCell.Value = i
        i = i + 1
    Next myCell
End Sub

Upvotes: 4

tigeravatar
tigeravatar

Reputation: 26660

You need to use an array if you want a setup like that. Here's how:

Replace your variable declaration statement

'Instead of your original
'Dim Range1 As Range, Range2 As Range, Range3 As Range, Range4 As Range

'Use this:
Dim arrRanges(1 To 4) As Range

Then change how you set the ranges:

Set arrRanges(1) = ws.Range("E6:E9")
Set arrRanges(2) = ws.Range("E15:E19")
Set arrRanges(3) = ws.Range("E21")
Set arrRanges(4) = ws.Range("E23")

And when you loop through them, it looks like this:

        For Each curCell In arrRanges(i).Cells

Upvotes: 5

Related Questions