Excel 2013 VBA loop: Reset sequential character when loop hits column

[jedwardo]

Hello everyone,

I'm adding sequential lettering to every cell greater than 0 in the loop, that part works fine. What I can't figure out how to do is reset the lettering to "a" every time "bcell" hits column D. The highlighted text is me fumbling around at a solution, without the blue text the loop works just fine.

Private Sub CommandButtonTest_Click()Dim bcell As Range
Dim c As String * 1                 'this and next line are alphabet counter
c = "a"


For Each bcell In Sheet2.Range("d3:ge50")


    With bcell
[COLOR=#0000ff]        If bcell = Sheet2.Range(D & bcell.Row) Then[/COLOR]
[COLOR=#0000ff]            c = "a"[/COLOR]
        ElseIf bcell.Interior.ColorIndex = 2 And bcell.Value > 0 Then
            bcell = c & bcell
            c = Chr(Asc(c) + 1)
        ElseIf bcell.Interior.ColorIndex = 53 And bcell.Value > 0 Then
            bcell = c & bcell
            c = Chr(Asc(c) + 1)
        ElseIf bcell.Interior.ColorIndex = 24 And bcell.Value > 0 Then
            bcell = c & bcell
            bcell.Offset(0, 1) = c & bcell.Offset(0, 1)
            c = Chr(Asc(c) + 1)
        End If
        
    End With


Next bcell


End Sub

Any help appreciated

Thanks,
Jordan

 

[djreiswig]

Do you want the values changed in the "D" cells or just want to change the value back to "a"?

 

[djreiswig]

This will just reset the value. The cells in column "D" will not be modified. This is in line with your example. I simplified your code a little with a Select Case to avoid duplication.

Private Sub CommandButtonTest_Click()
    Dim bcell As Range
    Dim c As String * 1                 'this and next line are alphabet counter
    c = "a"
    
    For Each bcell In Sheet2.Range("d3:ge50")
        With bcell
            If bcell.Column = 4 Then 'reset string to "a" if column D
                c = "a"
            ElseIf bcell.Value > 0 Then
                Select Case bcell.Interior.ColorIndex
                    Case Is = 2 Or 53
                        bcell = c & bcell
                        c = Chr(Asc(c) + 1)
                    Case Is = 24
                        bcell = c & bcell
                        bcell.Offset(0, 1) = c & bcell.Offset(0, 1)
                        c = Chr(Asc(c) + 1)
                End Select
            End If
        End With
    Next bcell
End Sub

 

[djreiswig]

Noticed you had a with statement you weren't using, so I modified the code to take advantage of that.

Private Sub CommandButtonTest_Click()
    Dim bcell As Range
    Dim c As String * 1                 'this and next line are alphabet counter
    c = "a"
    
    For Each bcell In Sheet2.Range("d3:ge50")
        With bcell
            If .Column = 4 Then 'reset string to "a" if column D
                c = "a"
            ElseIf .Value > 0 Then
                Select Case .Interior.ColorIndex
                    Case Is = 2 Or 53
                        .Value = c & .Value
                        c = Chr(Asc(c) + 1)
                    Case Is = 24
                        .Value = c & .Value
                        .Offset(0, 1) = c & .Offset(0, 1)
                        c = Chr(Asc(c) + 1)
                End Select
            End If
        End With
    Next bcell
End Sub

 

[jedwardo]

That worked like a charm thanks. I moved the bcell.column = 4 to my original code and it works fine. Changed the column to 3 and the range to (c3:ge50) because I did end up needing some values in D changed.

The code with the select case doesn't do anything when pressing the command button. No errors or anything. I'm not sure what to tweak to make it work because it all looks right and much cleaner than what I had.

 

[jedwardo]

Figured it out, weird. I made a 3rd case for colorindex 53 and now it works.

Private Sub CommandButtonTest_Click()    Dim bcell As Range
    Dim c As String * 1                 'this and next line are alphabet counter
    c = "a"
    
    For Each bcell In Sheet2.Range("c3:ge50")
        With bcell
            If .Column = 3 Then 'reset string to "a" if column D
                c = "a"
            ElseIf .Value > 0 Then
                Select Case .Interior.ColorIndex
                    Case Is = 2
                        .Value = c & .Value
                        c = Chr(Asc(c) + 1)
[COLOR=#0000ff]                    Case Is = 53[/COLOR]
                        .Value = c & .Value
                        c = Chr(Asc(c) + 1)
                    Case Is = 24
                        .Value = c & .Value
                        .Offset(0, 1) = c & .Offset(0, 1)
                        c = Chr(Asc(c) + 1)
                End Select
            End If
        End With
    Next bcell
End Sub

It was doing the same to me when I was putting color index 2 & 53 together like so earlier:

[COLOR=#333333]ElseIf bcell.Interior.ColorIndex = 2 Or [/COLOR][COLOR=#333333] bcell.Interior.ColorIndex = 53 Then [/COLOR]

Thanks again, looks much cleaner now!

 

[djreiswig]

Try this. I had the syntax for the select case wrong and it was skipping the first case.

Private Sub CommandButtonTest_Click()
    Dim bcell As Range
    Dim c As String * 1                 'this and next line are alphabet counter
    c = "a"
    
    For Each bcell In Sheet2.Range("c3:ge50")
        With bcell
            If .Column = 4 Then 'reset string to "a" if column D
                c = "a"
            ElseIf .Value > 0 Then
                Select Case .Interior.ColorIndex
                    Case 2, 53
                        .Value = c & .Value
                        c = Chr(Asc(c) + 1)
                    Case 24
                        .Value = c & .Value
                        .Offset(0, 1) = c & .Offset(0, 1)
                        c = Chr(Asc(c) + 1)
                End Select
            End If
        End With
    Next bcell
End Sub

 

[djreiswig]

A little shorter yet.

Private Sub CommandButtonTest_Click()
    Dim bcell As Range
    Dim c As String * 1                 'this and next line are alphabet counter
    c = "a"
    
    For Each bcell In Sheet2.Range("d3:ge50")
        With bcell
            If .Column = 4 Then 'reset string to "a" if column D
                c = "a"
            ElseIf .Value > 0 Then
                Select Case .Interior.ColorIndex
                    Case 2, 24, 53
                        .Value = c & .Value
                        If .Interior.ColorIndex = 24 Then .Offset(0, 1) = c & .Offset(0, 1)
                        c = Chr(Asc(c) + 1)
                End Select
            End If
        End With
    Next bcell
End Sub

 

[jedwardo]

That was it, much appreciated sir

 

[djreiswig]

You're welcome. Got a little rusty from not using Excel for a while.
Not sure if you saw my last post, as we posted almost the same time.