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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019
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,425
Office Version
  1. 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

Well-known Member
Joined
Oct 18, 2007
Messages
741
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
73,443
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,833
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,651
Messages
5,838,576
Members
430,557
Latest member
MK15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top