Alpha Numeric help

[Rick S.]

(Looks at a box.)
I'm stuck.

The code below reads the cell.address and I strip the "$" signs. Then I
test for character length and create a range, IE, "A1" or "Z99" etc. I
recently found myself using double alpha characters on one sheet, IE, "AA6".
I am not seeing how I can get the propper information to continue creating a
range I want.

As an example, a particular worksheet has data from A1 thru AA6, I attempt
to create a range from C5 thru AA7 with my current code but with the double
alpha address I get a range that is the opposite of my target, C6 thru A5 (6
total cells when it should be 48 total cells from C5 thru AA6).

As always, your help is appreciated!

'======
'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(sRow) = ""2"" " & sRow2 'for testing
Else
'double alpha characters fail, IE: address "AA7". 01.20.08
If Len(sRow) = "3" Then
sRow2 = Left(sRow, 2)
'MsgBox "Len(sRow) = ""2"" IsNumeric " & sRow2 'for testing
Else
sRow2 = Left(sRow2, 1)
'MsgBox "Len(sRow) = ""3"" " & sRow2 'for testing
End If
End If
'End FindCellRange=======================================
'======
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

 

[Per Jessen]

(Looks at a box.)
I'm stuck.

The code below reads the cell.address and I strip the "$" signs.  Then I
test for character length and create a range, IE, "A1" or "Z99" etc.  I
recently found myself using double alpha characters on one sheet, IE, "AA6".  
I am not seeing how I can get the propper information to continue creatinga
range I want.

As an example, a particular worksheet has data from A1 thru AA6, I attempt
to create a range from C5 thru AA7 with my current code but with the double
alpha address I get a range that is the opposite of my target, C6 thru A5 (6
total cells when it should be 48 total cells from C5 thru AA6).

As always, your help is appreciated!

'======
        '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 ExitFor
        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(sRow) = ""2"" " & sRow2 'for testing
        Else
'double alpha characters fail, IE: address "AA7".   01.20.08
            If Len(sRow) = "3" Then
                sRow2 = Left(sRow, 2)
                'MsgBox "Len(sRow) = ""2"" IsNumeric " &sRow2 'for testing
                Else
                sRow2 = Left(sRow2, 1)
                'MsgBox "Len(sRow) = ""3"" " & sRow2 'for testing
            End If
        End If
        'End FindCellRange=======================================
'======
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

Hi Rick

Assuming your data always start in cell A1 and all cells in used range
is filled I would do something like this:

lColumn = Range("A1").End(xlToRight).Column
lRow = Range("A1").End(xlDown).Row
tRange = Range("C6", Cells(lRow + 1, lColumn)).Address

Regards,

Per

 

[Rick S.]

I tested the code to see what it does and you are correct, at any empty cell
it stops collecting column addresses. But, if I enter a decimal point in
blank cells it works. I could live with that.
I will have to see how I can modify my code to utilize this.

Thank you.

--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

 

[Rick S.]

I see I left out some code to show what I am building from the initial code
posted.
'======
'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(sRow) = ""2"" " & sRow2 'for testing
Else
'double alpha characters fail, IE: address "AA7". 01.20.08
If Len(sRow) = "3" Then
sRow2 = Left(sRow, 2)
'MsgBox "Len(sRow) = ""2"" IsNumeric " & sRow2 'for testing
Else
sRow2 = Left(sRow2, 1)
'MsgBox "Len(sRow) = ""3"" " & sRow2 'for testing
End If
End If
'End FindCellRange======================
'Missing code, establishes my range from the disection above
Set rRngCol = Range("C5", Range(sRow2 & Rows.Count).End(xlUp))
'======
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

 

[Rick S.]

with minor modifications, this worked out well!

'====== 01.23.08 new code for addresses
Dim lColumn
Dim lRow2
Dim tRange

lColumn = Range("C5").End(xlToRight).Column
lRow2 = Range("C5").End(xlDown).Row
If lRow2 = "1048576" Then
lRow2 = Range("C5").End(xlUp).Row
lRow2 = lRow + 4
End If
tRange = Range("C5", Cells(lRow2, lColumn)).Address
'MsgBox "lColumn " & lColumn 'for testing
'MsgBox "lRow2 " & lRow2 'for testing
'MsgBox "tRange " & tRange 'for testing
'End FindCellRange

Set rRngCol = Range(tRange)
'======
As for empty cells, I filled them with "n/a", solves that problem.

Thanks Per!
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007