VBA Next Alphabet Char based on previous selected cell

Winterfest

New Member
Joined
Mar 13, 2019
Messages
2
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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
Am no VBA expert so Im looking at a formula solution.

Example data, expected output?
 

Winterfest

New Member
Joined
Mar 13, 2019
Messages
2
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
1Step 1 Things we are making
a
b
c
d
e

<tbody>
</tbody>
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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

Well-known Member
Joined
Oct 18, 2007
Messages
697
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,578
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
Formula:

A​
B​
C​
D​
1​
1Step 1
2​
3​
a​
A3: =IF(MATCH(999, A$1:A2) > IFERROR(MATCH("z", A$1:A2), 0), "a", CHAR(CODE(INDEX(A$1:A2, MATCH("z", A$1:A2) + 1))))
4​
5​
b​
6​
7​
c​
8​
9​
10​
2Step 2
11​
12​
a​
13​
14​
b​

Copy the formula in the first cell to each cell where needed.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,424
Messages
5,528,682
Members
409,830
Latest member
KT50

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top