CODEWITHSUNDEEP
excelvba
Jrules80
Jrules80

Reputation: 178

VBA translate column value in alphabet to a numeral

How do I convert the alpha part of an excel address "$C$2" to 3 and 2 so that I could re-use it in a cell object.

If it is "$E$4", then I need two separate values like 5 (for the letter E) and 4, so that I could reference that using the object - Cells(4,5)

Basically, I am trying to un-merge cells using this code below and that is where the need to get the numeral of the excel cell came about.

Sub UnMerge()
    Dim i As Integer
    Dim fromRange() As String
    Dim toRange() As String
    Dim temp() As String

    ActiveSheet.UsedRange.MergeCells = False
    fromRange() = Split(ActiveCell.Address, "$")
    temp() = Split(Selection.Address, ":")
    toRange() = Split(temp(1), "$")


    For i = fromRange(2) To toRange(2)
        If Cells(i, Range(temp(0)).Column) = "" Then
            Cells(i, Range(temp(0)).Column) = Cells(i - 1, Range(temp(0)).Column).Value
        End If
    Next i
End Sub

Upvotes: 0

Views: 66

Answers (2)

Scott Craner
Scott Craner

Reputation: 152605

changing and spliting strings to get to numbers is slow. Just use the selection.rows and selection.column:

Sub UnMerge()

    Selection.MergeCells = False
    With ActiveSheet
        Dim i As Long
        For i = Selection.Row To Selection.Rows.Count + Selection.Row - 1
            If .Cells(i, Selection.Column) = "" Then
                .Cells(i, Selection.Column) = .Cells(i - 1, Selection.Column).Value
            End If
        Next i
    End With
End Sub

Upvotes: 1

Michal
Michal

Reputation: 6099

Debug.Print Range("$E$4").Row & ", " & Range("A1").Column

Upvotes: 1

Related Questions