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

 

[jedwardo]

Worked perfect, I created a bid with everything selected and it sequenced them all without special characters or crashing.

 

[Peter_SSs]

Private Sub CommandButtonAddSeqChar_Click()    Dim i As Integer
    Dim vcell As Range
    Dim bcell As Range
'    Dim c As String * 1                'this and next line are alphabet counter
'    c = "a"
    Dim c As String
    [COLOR="#FF0000"][B]c = LCase(Replace(Range(c & 1).Offset(, 1).Address(0, 0), 1, ""))[/B][/COLOR]
    i = 1


    
    For Each vcell In Range("b3:b50")
        With vcell
            If .Value > 0 Then
                Select Case .Value
                    Case "A", "B", "C", "D"
                        .Value = .Value & i
                        i = i + 1
                End Select
            If .Value < .Offset(1, 0).Value Then
                i = 1
            End If
            End If
        End With
    Next vcell


    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 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

Ok, figured it out while typing this, after telling you pop-up said c = "" I realized I shouldn't have comment blocked c = a.

Worked perfect, I created a bid with everything selected and it sequenced them all without special characters or crashing.

I don't believe that it will work correctly still (even after uncommenting the c="a") if you have a lot of cells in C3:GE50 that meet the conditions. To test, fill every cell in C3:GE3 with the number 2 and fill them all with ColorIndex 24 say then run the code.



My instruction before was:

Just use it in place of the one I highlighted red earlier.

However, you have used it in a different place.

The code should be

Private Sub CommandButtonAddSeqChar_Click()
    Dim i As Integer
    Dim vcell As Range
    Dim bcell As Range
    Dim c As String
    c = "a"
    i = 1

    For Each vcell In Range("b3:b50")
        With vcell
            If .Value > 0 Then
                Select Case .Value
                    Case "A", "B", "C", "D"
                        .Value = .Value & i
                        i = i + 1
                End Select
            If .Value < .Offset(1, 0).Value Then
                i = 1
            End If
            End If
        End With
    Next vcell


    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 2, 53
                        .Value = c '& .Value
                        c = LCase(Replace(Range(c & 1).Offset(, 1).Address(0, 0), 1, ""))
                    Case 24
                        .Value = c '& .Value
                        .Offset(0, 1) = c '& .Offset(0, 1)
                        c = LCase(Replace(Range(c & 1).Offset(, 1).Address(0, 0), 1, ""))
                End Select
            End If
        End With
    Next bcell
End Sub

 

[jedwardo]

Haha yeah I caught that too and changed it the way you have in your last post. I did some test runs with every possible selection I have on my bid sheet chosen and it sequences all the way up to "cz" before it has nowhere else to go. I comment blocked the line that resets c = "a" at column 3 for giggles and it sequenced all the way up to "pa".