How do I use Loop with Variable Range

Question

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

For Each curCell In Range("E6:E9,E15:E19,E21,E23").Cells would work the same way — Tim Williams
– Tim Williams, Commented Feb 16, 2016 at 19:19
Thank you, Tim. I was trying to avoid having the cells referenced within the code so that it would be easier to debug for the next person taking over. I had forgotten that you could set up a non-contiguous range in this way. Thanks for the reminder! — SAS_N00b
– SAS_N00b, Commented Feb 16, 2016 at 19:59

tigeravatar · Accepted Answer · 2016-02-16 19:16:19Z

5

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

answered Feb 16, 2016 at 19:16

tigeravatar

26.7k5 gold badges32 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SAS_N00b Over a year ago

Thank you, @tigeravatar ! This is exactly what I was looking for. I really appreciate the quick response.

John Coleman · Accepted Answer · 2016-02-16 19:22:05Z

4

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

answered Feb 16, 2016 at 19:22

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

1 Comment

SAS_N00b Over a year ago

Thank you, @John Coleman . This is another good option. I had forgotten that you could assign non-contiguous ranges to a single variable. Thank you for the reminder and solution.

brettdj · Accepted Answer · 2016-02-17 03:47:25Z

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

Collectives™ on Stack Overflow

How do I use Loop with Variable Range

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related