Row and Column from a Cell Input

[helpexcel]

Hi - I have a spreadsheet where the user put in the starting cell of data that's to be used. So they would put "G7" in cell B2. I'm trying to get code that will split that G7 into row and column. I was using the code below that had the user enter the row and column separately, but it's much simpler to have them enter both together. Any help is appreciated.

Dim rng As Range
Dim colFR As Variant
With ws2
     colFR = .Range("B2")
    Set rowFR = .Range("D2")
End With

Dim LastRow As Long
With ws1
    LastRow = .Cells(.Rows.Count, colFR).End(xlUp).Row
    Set rng = .Range(.Cells(rowFRD, colFR), .Cells(LastRow, colFR))
    rng.Copy ws3.Range("A3")
End With

 

[James006]

Hi,

You will a couple of UDFs to Extract Letters and to Extract Numbers

Option Explicit

Function ExtractLetters(var) As String
Dim i As Long
Dim result As String
    For i = 1 To Len(var)
        If Asc(Mid(UCase(var), i, 1)) >= 65 And Asc(Mid(UCase(var), i, 1)) <= 90 Then
            result = result & Mid(var, i, 1)
        End If
    Next i
    ExtractLetters = result
End Function

Function ExtractNumbers(var) As Double
Dim i As Long
Dim result As String
    For i = 1 To Len(var)
        If Asc(Mid(UCase(var), i, 1)) >= 48 And Asc(Mid(UCase(var), i, 1)) <= 57 Then
            result = result & Mid(var, i, 1)
        End If
    Next i
    ExtractNumbers = result
End Function

 

[helpexcel]

Thanks for the response. I see how the top one is getting the column and the other the row. Would you mind explaining how I would then use this in the creating ranges? And which portion of the code I would manipulate if I have multiple cells with an cell address reference?

 

[James006]

Whether in your spreadsheet or with a macro, both functions can be used with :
= ExtractLetters(B2) and = ExtractNumbers(B2)

 

[helpexcel]

Got it, works great thanks!

Is there an easier way to use this in the range copy code I have?

 

[James006]

Hi,
In order to simplify ...
You said the input in cell B2 would be "G7" ...
Could the first letter go beyond Z ... or not ?

 

[helpexcel]

There are time that a cell might be blank, so there is nothing to extract. How do I stop this code from erroring out and just move on?

 

[helpexcel]

Hi,
In order to simplify ...
You said the input in cell B2 would be "G7" ...
Could the first letter go beyond Z ... or not ?

Probably not.

 

[James006]

OK ...
Both UDF's do allow to extract all Letters and Numbers
In order to get the Column Number extracted from cell B2 in another cell, you can test folllowing formula :

 

[helpexcel]

yep, It works great. would like to adjust it slightly so that it it doesn't error when trying to extract data from a blank cell.