Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible to format a cell to keep alpha characters only? | Excel Discussion (Misc queries) | |||
remove alpha or non-numeric characters from cell | Excel Discussion (Misc queries) | |||
Need to add figures based on alpha characters in the same cell | Excel Worksheet Functions | |||
Can you ID a cell that has both Alpha AND Numeric characters? | Excel Worksheet Functions | |||
Check for Alpha characters | Excel Discussion (Misc queries) |