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
Rick S. wrote:
>
> [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
|
Similar Threads
