Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alpha/Numeric help
[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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alpha/Numeric help
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Alpha with numeric and numeric only numbers in a column | Excel Discussion (Misc queries) | |||
If statement for alpha numeric combo AND numeric only | Excel Discussion (Misc queries) | |||
Alphanumeric Sorting - numeric alpha numeric | Excel Worksheet Functions | |||
only extract numeric value from alpha numeric cell | Excel Discussion (Misc queries) | |||
The colums changed from alpha to numeric how do you make it alpha | Excel Discussion (Misc queries) |