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]

Nope didn't see that one, even better. I'll have another problem here posted before long I'm sure haha

 

[Peter_SSs]

I'm adding sequential lettering ....

For Each bcell In Sheet2.Range("[COLOR="#0000CD"][B]d3:ge50[/B][/COLOR]")


            [COLOR="#FF0000"][B]c = Chr(Asc(c) + 1)[/B][/COLOR]

Clearly I haven't seen the data but that red line is only going to give "letters" for 26 values before giving other symbols and eventually crashing if more than 161 values in the row meet the condition & I note the blue range contains 184 cells. I guess your data just doesn't ever have many cells > 0 with the particular interior colour?

 

[jedwardo]

Your right, the 184 cells are all possible items that could be included in each section of the bid ie: A1a, A1b, A1c etc.. I rarely have more than 26 items in a room that are bid on but it does happen on occasion. I may need to come up with an alternate method of labeling the bids here. Is there a quick way to modify that red line to have it carry on as A1aa, A1ab, A1ac and so on after the 26 characters are exceeded?

 

[Peter_SSs]

Is there a quick way to modify that red line to have it carry on as A1aa, A1ab, A1ac and so on after the 26 characters are exceeded?

You have to also modify your Dim statement. Try this sort of structure for c. It will go a-z then aa-az then ba-bz etc, just like the sheet column labels.

Dim c As String
c = "a"
    
.
.
.
c = LCase(Replace(Range(c & 1).Offset(, 1).Address(0, 0), 1, ""))
.
.

 

[jedwardo]

c = LCase(Replace(Range(c & 1).Offset(, 1).Address(0, 0), 1, ""))

That is an extremely humbling line of code, I'm not sure I know how to implement that. How would I modify the Dim statement?</pre>

 

[Peter_SSs]

That is an extremely humbling line of code, I'm not sure I know how to implement that.

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

How would I modify the Dim statement?

.. to what I posted in post #14

 

[jedwardo]

Haha, I see it now

 

[jedwardo]

That one gives me a run time error 1004, method range of object _worksheet failed with the "c = Lcase" line highlighted.

 

[Peter_SSs]

That one gives me a run time error 1004, method range of object _worksheet failed with the "c = Lcase" line highlighted.

Could you ..
a) Post the whole code that you are using now.
b) Run it again and when it errors, Debug & hover your cursor over the variable c in that line and report what the little pop-up box has in it.

 

[jedwardo]

Hi Peter here's the code. The pop-ups on both c's say c = ""

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
    c = LCase(Replace(Range(c & 1).Offset(, 1).Address(0, 0), 1, ""))
    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. I never knew you could do that in debug. Thanks for the code Peter and Happy holidays