VBA split cell address

Question

I have declared a constant to identify the start address of a block of data.

For example:

Const addStartData = "E131"

I now wish to split this into two variables, but there is no delimiter.

Dim cc as String  ' Column

Dim rr as Long     ' Row

I particularly need the value of rr to control a loop.

How can I achieve this?

Answer 1

Or you could simply remove the "$"s from the string at the end:

    strInput = "$E$131"

    lnRow = Range(strInput).Row

    strCol = Left(strInput, Len(strInput) - Len(CStr(lnRow)))

strCol = Replace(strCol, "$", "")

or just:

    lnRow = Range(strInput).Row

    strCol = Replace(Left(strInput, Len(strInput) - Len(CStr(lnRow))), "$", "")

Cheers

Rich

Answer 2

Or you could simply use:

    lnRow = Range(strAddStartData).Row

    strCol = Left(strAddStartData, Len(strAddStartData) - Len(CStr(lnRow)))

but the ColumnLetter function is often useful.

Cheers

Rich

Answer 3

There are a number of options; one easy one would be to use that cell address against the activesheet to return column and row separately, e.g.:

(aircode)

Dim rng as range

Dim cc as Long

Dim rr as Long

Set rng = Activesheet.range(addStartData)

cc= rng.column

rr=rng.row

if you wanted to turn cc back into a string, you could use something like

Function ColLetter(cc As Long) As String

    On Error Resume Next

    ColLetter = Application.Substitute(Application.ConvertFormula("R1C" & cc, xlR1C1, xlA1, 4), "1", "")

End Function

Hope that helps!

Edit: Silly MS slow updating, it looked like no-one had responded yet :-)

Answer 4

Hi,

Try this:

Sub Test()

    Const strAddStartData As String = "E131"

    Dim strCol As String  ' Column

    Dim lnRow As Long     ' Row

On Error Resume Next    Dim rng As Range    Set rng = Range(strAddStartData)    If rng Is Nothing Then MsgBox "strAddStartData does not contain a valid cell address!": Exit Sub    On Error GoTo 0   

    strCol = ColumnLetter(Range(strAddStartData).Column)

    lnRow = Range(strAddStartData).Row

    Debug.Print strCol, lnRow

End Sub

Function ColumnLetter(lnColNum As Long) As String

' Returns the column letter(s) when fed a column number.'    Dim ws As Worksheet

    Set ws = Worksheets(1)

    ColumnLetter = Mid(ws.Columns(lnColNum).Address, _

        InStrRev(ws.Columns(lnColNum).Address, "$") + 1)

End Function

The bit in italics (On Error Resume Next .... On Error GoTo 0) will check for a valid cell address in strAddStartData, but you could delete it if you know strAddStartData will always contain a sensible cell address.

Hope that helps.

Cheers

Rich