Alpha/Numeric help

[Rick S.]

[looks at box]
I'm stuck.
[/Looks at box]

I have a worksheet where the cell range has gone into double alpha
characters (IE "AA6") and I can't seem to find a way to work with it in my
code.

I retrieve a cell address and manipulate it to create ranges, once my cells
get into double alpha characters it will fail. For an example, one work
sheet has cells from "A5" through "AA23", with my code I end up with a range
of "C5 through "A23" instead of "C5" through "AA23". I understand why, but I
don't see how to fix it.

Thanks in advance.

'======
'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 "AA6". 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

 

[Dave Peterson]

Don't build a string of addresses. It's too much work. But I'm confused at
what you're doing. I'm not sure if you included test code in your post.

But this may help (or not!)...

When I want to loop through a range and determine a subset of that range:

Dim myRng as range
dim myCell as range
dim myRngOk as range

set myrng = somerangehere
set myrngok = nothing
for each mycell in myrng.cells
if mycell.value = something then
'add it to the ok rng
if myrngok is nothing then
set myrngok = mycell
else
set myrngok = union(myrngok,mycell)
end if
end if
next mycell

then I can use:

if myrngok is nothing then
msgbox "no cells found"
else
msgbox myrngok.address
'or
msgbox myrngok.entirerow.address
end if

[looks at box]
I'm stuck.
[/Looks at box]

I have a worksheet where the cell range has gone into double alpha
characters (IE "AA6") and I can't seem to find a way to work with it in my
code.

I retrieve a cell address and manipulate it to create ranges, once my cells
get into double alpha characters it will fail. For an example, one work
sheet has cells from "A5" through "AA23", with my code I end up with a range
of "C5 through "A23" instead of "C5" through "AA23". I understand why, but I
don't see how to fix it.

Thanks in advance.

'======
'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 "AA6". 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