VBA Next Alphabet Char based on previous selected cell

Winterfest · Mar 13, 2019

Hello!

I have tried to do this using a formula, but sometimes the rows are separated by a few blank.

How would i go about having a macro only work with the selected range of cells, look at the row above and add 1 to the CHAR. Thats what i am finding really tricky to figure out.
If i need to be more clear, I will be:
I select cells A2, A3, A5, A8, A9 etc.

No real order to when there are blanks between the rows, which i s why i was wondering if i can get it to only work with the selected ones. I can do this manually, shouldn't be a problem.
Otherwise, i was going to see if i could build in something that would avoid Selection.SpecialCells(xlCellTypeBlanks).

Any advice would be appreciated

Special-K99 · Mar 13, 2019

Am no VBA expert so Im looking at a formula solution.

Example data, expected output?

Winterfest · Mar 13, 2019

Hey Special-K99,

This is for a instruction sheet, where column A is lettered steps (a,b,c,d,e...) during editing, i sometimes remove steps of move them around, and was looking for a way to quickly redo the lettering.
I have attempted to use =CHAR(CODE(A1)+1), but haven't gotten the results i wanted, due to sometimes having steps that use 2-4 rows, and only the first row of that step has the letter.
Can't attach a workbook to give you a great example, but i can try to make a table here to show you
[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD]1[/TD]
[TD]Step 1 Things we are making[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Special-K99 · Mar 13, 2019

Your VBA expertise seems better than mine, think I'll have to leave this to someone else to solve.
Doubt a formula would work looking at selection of cells

mse330 · Mar 13, 2019

Hey Winterfest,

Try the below code ... Is this what you need ? If yes, do you have more than 26 steps ?

Code:

Sub ReOrder()


Dim Col As String, Cnt As Long, fRow As Long, lRow As Long


Col = InputBox("Please enter the Column letter where you would like to Re-Order your data")
fRow = InputBox("Please enter the number of first row of your alphabets")
lRow = ActiveSheet.Range(Col & Rows.Count).End(xlUp).Row


Cnt = 65 ' change this to 97 in case you want lower case letters
For x = fRow To lRow
    If Cells(x, Col).Value <> "" Then
        Cells(x, Col).Value = Chr(Cnt)
        Cnt = Cnt + 1
    End If
Next
    
End Sub

Fluff · Mar 13, 2019

How about

Code:

Sub Winterfest()
   Dim i As Long
   Dim Cl As Range
   
   i = 65
   For Each Cl In Range("A:A").SpecialCells(xlConstants)
      If Not IsNumeric(Cl) Then
         Cl.Value = Chr(i)
         i = i + 1
      Else
         i = 65
      End If
   Next Cl
End Sub

This will reset the letters to A whenever there is a number in col A.
And as mse330 said, will you ever go above Z

shg · Mar 13, 2019

Formula:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A

[/td][td="bgcolor:#C0C0C0"]

B

[/td][td="bgcolor:#C0C0C0"]

C

[/td][td="bgcolor:#C0C0C0"]

D

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1

[/td][td]1[/td][td]Step 1[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2