VBA Case Else working / Case not working

[BenGee]

Dim c As Range
Dim i As Long

For Each c In Range("C1:Z1")
   For i = 2 to 26
     Select case i
       Case 7, 12, 17, 22
         c.Value = "=" & c.Offset(0, -1).Address & "+3"
       Case Else
         c.Value = "=" & c.Offset(0, -1).Address & "+1"
     End Select
  Next
Next

The above just applies case else to the whole range, rathe than "+3" for the cases I've tried to specify / do something different to. Any idea what I'm missing would be hugely appreciated.

Thank you

 

[steve the fish]

Cant say as im entirely sure what you are trying to do there. What is happening is that for each range c you are looping through i (2 to 26) and overwriting each result each time so as the loop finishes at 26 you will only ever get the result of i = 26 which is Case Else.

 

[Rick Rothstein]

Dim c As Range
Dim i As Long

For Each c In Range("C1:Z1")
   For i = 2 to 26
     Select case i
       Case 7, 12, 17, 22
         c.Value = "=" & c.Offset(0, -1).Address & "+3"
       Case Else
         c.Value = "=" & c.Offset(0, -1).Address & "+1"
     End Select
  Next
Next

Steve told you why you are getting the result you mentioned, what we need to know is what the loop variable "i" represents. Is it a row number, a row offset value or something else? What you need to do is make clear to us why you are doing that inner loop.

 

[BenGee]

Thank you Steve and Rick, your help is appreciated. And apologies for being unclear here.

The "i" represents a column number. The goal is through columns 2 to 26, the value of each cell will be the same. Except for columns 7, 12, 17, 22 where the value needs to be different.

This achieves what I need it do albeit appreciate this doesn't seem the same;

    For Each c In Range("C1:Z1")
        For i = 2 To 26
            Select Case i
                Case 7, 12, 17, 22
                Case Else
                    c.Value = "=" & c.Offset(0, -1).Address & "+1"
            End Select
        Next
    Next
    
    For Each c In Range("G1,L1,Q1,V1")
        c.Value = "=" & c.Offset(0, -1).Address & "+3"
    Next

 

[steve the fish]

Again that is confused. You dont seem to have understood what i was saying. You dont need two loops here. This is how you could rewrite yours:

For Each c In Range("C1:Z1")
    Select Case c.Column
        Case 7, 12, 17, 22
            c.Value = "=" & c.Offset(0, -1).Address & "+3"
        Case Else
            c.Value = "=" & c.Offset(0, -1).Address & "+1"
    End Select
Next

You could use a formula like this:

=B1+CHOOSE((MOD(COLUMN(C1)-2,5)=0)+1,1,3)