CODEWITHSUNDEEP
excelvba
Christian
Christian

Reputation: 129

VBA: Convert range to cell numbers e.g. (1,1)

By using this code I get a range selected in my worksheet. E.g. "BE5:BE182" I get as a return from this code: 

MsgBox "Selected range: " & Selection.Areas(1).Address(False, False)

Now I want my retrive BE5 and translate that into numeric values for both col. and row (e.g. 1,1), and I also want to do this for BE182. How do I do that?

Ambition: My goal with the macro is to select a range where numeric data should be appended. Based on the row count between start of range and end of range I will always use col 1 (A) where country name are stored. Against these I want do achive a similar VLOOKUP-function, against data from another workbook - where the user also should select a range from where data should be taken from.

The idea of letting the user define col. and row-range is that the row-range against vlook-up can change and that the col maybe not always is just +1.

Many thanks in advance!

Upvotes: 0

Views: 3954

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

How about:

Sub RowAndColumn()
    Dim r1 As Range, r2 As Range, addy As String

    addy = Selection.Address(0, 0)
    If Selection.Count = 1 Then
        MsgBox Selection.Row & "," & Selection.Column
    Else
        arr = Split(addy, ":")
        Set r1 = Range(arr(0))
        Set r2 = Range(arr(1))
        MsgBox r1.Row & "," & r1.Column & vbCrLf & r2.Row & "," & r2.Column
    End If
End Sub

Upvotes: 0

Bill the Lizard
Bill the Lizard

Reputation: 405765

You can use the Row and Column properties of the range to get the numeric bounds of the range.

Dim Selected As String
Dim SelectedRange() As String
Dim BeginSel As String
Dim EndSel As String

' Get the range of cells selected by the user.
Selected = Selection.Areas(1).Address(False, False)
MsgBox "Selected range: " & Selected

' Split the selected range into begin and end cells.
SelectedRange = Split(Selected, ":")
BeginSel = SelectedRange(0)
EndSel = SelectedRange(1)

MsgBox "Begin Selected range: " & BeginSel
MsgBox "End Selected range: " & EndSel

' Convert the begin and end cells to row and column values
MsgBox Range(BeginSel).Row & ", " & Range(BeginSel).Column
MsgBox Range(EndSel).Row & ", " & Range(EndSel).Column

Here I split the selected range on the ":" character to get the begin and end cells of the range. You'll probably want to check the length of the array returned by the Split function to make sure the user has selected an actual range of cells instead of a single cell (or nothing).

This converts the selected range A1:E3 into the coordinates (1, 1) and (3, 5), for example.

Upvotes: 1

Related Questions