PDA

View Full Version : [SOLVED] Start From Last Column - Sequential Number



dj44
03-27-2018, 02:16 PM
good day,

now i know how to sequentially number, that works well







ws.Range("G7:AC7").Cells = "###"

For Each oCell In ws.Range("G7:AC7").Cells
oCell.Replace What:="###", Replacement:=i
i = i + 1

Next oCell


I wanted to know what was the mechanism for me to number in reverse

instead of 0,1,2,
G7, H7,I7

could i make

0,1,2,
Ac7, AB7, AA7 etc


start numbering from my last column to the first

well i couldnt find anything, im sure it may be something simple?

Paul_Hossler
03-27-2018, 03:06 PM
You mean something like this?

G7 = 23
AC7 =1




Option Explicit

Sub test()
Dim i As Long
Dim oCell As Range

ActiveSheet.Range("G7:AC7").Cells = "###"
i = ActiveSheet.Range("G7:AC7").Cells.Count

For Each oCell In ActiveSheet.Range("G7:AC7").Cells
oCell.Replace What:="###", Replacement:=i
i = i - 1
Next oCell

End Sub




or

G7 = 22
AC7 =0





Option Explicit
Sub test()
Dim i As Long
Dim oCell As Range

ActiveSheet.Range("G7:AC7").Cells = "###"
i = ActiveSheet.Range("G7:AC7").Cells.Count - 1

For Each oCell In ActiveSheet.Range("G7:AC7").Cells
oCell.Replace What:="###", Replacement:=i
i = i - 1
Next oCell

End Sub

dj44
03-27-2018, 03:49 PM
Hello Paul,

nice to see you.

i only managed to get the i = i - 1 correct

but i couldnt work out the cells in reverse :doh:

its all simple untill i get my hands on it

thanks for the code and great eve