Check cell address for double alpha characters
 

Hmmm. I have posted this two times now and they do not show up in this NG
(1-10+ hours past)!? "Alpha Numeric Help" and "Alpha/Numeric help"???

I retrieve cell addresses and manipulate them to create ranges, I have run
into a problem when the cell address has double alpha characters, example
"AA23". In my code below I should get a range of "C5 through "AA23", due to
the double alpha characters in "AA23" I get a range of "C5 through A23".
My true used range is "A1" through "AA23".
Based on my code below, how can I handle ranges with double alpha characters?

'======
'Begin FindCellRange=====================================
Dim x As Long, SRng As Range, Rcount As Long
Dim sRange
Dim sRow As String
Dim sRow2 As String

Set SRng = ActiveSheet.UsedRange
Rcount = SRng.Rows.Count
For x = Rcount + SRng.Row To 1 Step -1
If Application.CountA(SRng.Rows(x)) < 0 Then Exit For
Next x
'MsgBox "First empty row is " & SRng.Rows(x + 1).Address(0, 0) 'for
testing value
sRow = Right(SRng.Rows(x + 1).Address(0, 0), 3)
sRow = Replace(sRow, ":", "")
If Len(sRow) = "2" Then
sRow2 = Left(sRow, 1)
'MsgBox "Len(sRow2) = ""2"" " & sRow2 'for testing
Else
If Len(sRow) = "3" Then 'double alpha characters fail, IE:
address "AA23". 01.20.08
sRow2 = Left(sRow, 2)
sRow2 = Left(sRow2, 1)
'MsgBox "Len(sRow2) = ""3"" " & sRow2 'for testing
End If
End If
'End FindCellRange===================================== ==
'======

--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

This time, I coppied this entire post to a text file, including topic and
discussion Group.

Check cell address for double alpha characters
 

There have been problems with posts showing up--but see your earlier post.

Rick S. wrote:

Hmmm. I have posted this two times now and they do not show up in this NG
(1-10+ hours past)!? "Alpha Numeric Help" and "Alpha/Numeric help"???

I retrieve cell addresses and manipulate them to create ranges, I have run
into a problem when the cell address has double alpha characters, example
"AA23". In my code below I should get a range of "C5 through "AA23", due to
the double alpha characters in "AA23" I get a range of "C5 through A23".
My true used range is "A1" through "AA23".
Based on my code below, how can I handle ranges with double alpha characters?

'======
'Begin FindCellRange=====================================
Dim x As Long, SRng As Range, Rcount As Long
Dim sRange
Dim sRow As String
Dim sRow2 As String

Set SRng = ActiveSheet.UsedRange
Rcount = SRng.Rows.Count
For x = Rcount + SRng.Row To 1 Step -1
If Application.CountA(SRng.Rows(x)) < 0 Then Exit For
Next x
'MsgBox "First empty row is " & SRng.Rows(x + 1).Address(0, 0) 'for
testing value
sRow = Right(SRng.Rows(x + 1).Address(0, 0), 3)
sRow = Replace(sRow, ":", "")
If Len(sRow) = "2" Then
sRow2 = Left(sRow, 1)
'MsgBox "Len(sRow2) = ""2"" " & sRow2 'for testing
Else
If Len(sRow) = "3" Then 'double alpha characters fail, IE:
address "AA23". 01.20.08
sRow2 = Left(sRow, 2)
sRow2 = Left(sRow2, 1)
'MsgBox "Len(sRow2) = ""3"" " & sRow2 'for testing
End If
End If
'End FindCellRange===================================== ==
'======

--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

This time, I coppied this entire post to a text file, including topic and
discussion Group.

--

Dave Peterson