For loop with non-contiguous range

Question

I have a loop that runs through each column and sets the value to an R1C1 sum of a few rows above it. I'm using a for loop, but I want to skip a few columns, as they contain formulae already in the cell. How can I set up a loop that only cycles through a non-contiguous set or numbers?

For reference, I want it to cycle through columns 1 to 80, but skip cols 25, 36, 37, 44, 60, 63, 64, 67, 68, 73, 75 and 76.

Edit: thanks guys, but I;ve already got it working as you described; I was looking for a shorter and more elegant method.

Edit 2: Is that even VBA?!

Answer 1

I'd do one of two things. The first is to only execute the body for specific loop numbers:

for i = 1 to 70
    skipIt = false
    skipIt = skipIt or (i = 25)
    : : :
    skipIt = skipIt or (i = 76)
    if not skipIt then
        ' Put your body here. '
    end if
next

Or, you can jump straight to the next:

for i = 1 to 70
    if i = 25 goto nextIter
    : : :
    if i = 76 goto nextIter

    ' Put your body here. '
nextIter:
next

Answer 2

you could put an if in the beginning of your loop, and skip the columns you don't want to iterate through

Answer 3

  for i = 1 to 70
  {
    switch (i)
    {
      case 25, 36, 37, 44, 60, 63, 64, 67, 68, 73, 75 76 : break;
      default : // process break;
    }
  }

Answer 4

A VBA version of Learning's C# answer:-

Dim col As Integer: For col = 1 To 70

    Select Case col

    Case 25, 36, 37, 44, 60, 63, 64, 67, 68, 73, 75, 76
        'do nothing'

    Case Else
        'do something'

    End Select

Next col

Answer 5

You can set up an array containing the column numbers that you need to process.

Your loop can then loop over the array values.

Another solution is to define a boolean array with a value for every column and mark the columns that you want to skip. In the loop over all the columns check the boolean array.

Yet another solution is if you can detect in the loop whether the column is a column that you need to skip (by detecting the formula that is there).

Answer 6

This approach uses Excel's range object functionality.

Define your range using Excel's UNION method to be a range of non-contiguous of columns.

To speed up the loop, you can reduce the range to include only those cells which contain formulas using the SpecialCells method of the range object.

Function LoopColumns() Dim Target As Range Dim Cell As Range

' Create a range object based on the Union of the columns you want Set Target = Union(Range("A:D"), Range("F:G"), Range("J:L"), Range("P:Q"))

' If you only want to process formulas, reduce the range again but having ' Excel define the range as only those cells which contain a formula Set Target = Target.SpecialCells(xlCellTypeFormulas, 1)

For Each Cell In Target.Cells
    'process cells here...
Next Cell

End Function