Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays problem in new filtering code
I use Excel '03. There are two sheets with different projects we are working
on. Column E on both sheets have identical information; an alpha-numeric code formated xxxxx-xxxxx. What I am trying to make happen is when I click on Sheet 2, it filters column E to those codes that are currently being displayed on Sheet 1 through the activate event. Sheet 1 has a seperate column with project owners names on it that I use to filter the sheet by one name at a time and look at the information for all of the owners projects. One code per project. I am trying to get all the codes from Sheet 1 to store into an array but my loop exits when I get to the last cell in the range. I am trying to use a dynamic array that uses range(e65536).end(xlup).address as the upper limit of the array. Here is the code. Thanks for any help. Dim CCLimit As Integer Dim CCIndex() As String Dim MyRange As Object Dim EndPoint As Variant Dim Count As Long, i As Long Dim R As Object CCLimit = GetLimit() ReDim CCIndex(1 To CCLimit) i = 1 Count = 6 EndPoint = Worksheets("Sheet1").Range("E65536").End(xlUp).Add ress Set MyRange = Worksheets("Sheet1") MyAddress = MyRange.Range("E" & Count).Address Do If Sheets("Sheet1").Rows(Count).Hidden = False Then CCIndex(i) = Left(MyRange.Range("E" & Count).Value, 11) MsgBox CCIndex(i) i = i + 1 End If Count = Count + 1 MyAddress = MyRange.Range("E" & Count).Address Loop Until MyAddress = EndPoint All I need to know at this point is how to get the array to work. I tried to change Loop Until MyAddress = EndPoint to Loop Until MyAddress EndPoint but that did not work. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays problem in new filtering code
Why are you using such an awkward loop structure. You know the limits you
want to iterate between, so why not just use a For..Next loop instead to loop between them? Starting with your i=1 statement, here is some revised code for you to consider... i = 1 Set MyRange = Worksheets("Sheet1") EndPoint = MyRange.Cells(MyRange.Rows.Count, "E").End(xlUp).Row For Count = 6 To EndPoint If Sheets("Sheet1").Rows(Count).Hidden = False Then CCIndex(i) = Left(MyRange.Range("E" & Count).Value, 11) i = i + 1 End If Next Note that I made a couple of changes (personal preference)... I changed your EndPoint variable to hold the Row number rather than the address. I then use that as the upper limit in the For..Next loop that follows (the lower limit was set at 6 which is what your code showed your starting your count at). I also changed your Range("E65536") reference to a Cells call and let VB calculate the 65536 number via Rows.Count (again, personal preference... I never can remember that number on my own<g). Also, since you set the worksheet in the MyRange variable (why that name... it is not a range, it is a worksheet), I reversed the Set statement and EndPoint assignment statement in order to take advantage of the Set variable (doing this allows me to use MyRange instead of having Worksheets("Sheet1") repeated twice). Anyway, the main point of the code it that I think the For..Next loop is cleaner and easier to construct/maintain than the Do..Loop you originally posted. Note that you might be able to eliminate the declaration for MyAddress if you don't make use of it anywhere else in your code. -- Rick (MVP - Excel) "Steve" wrote in message ... I use Excel '03. There are two sheets with different projects we are working on. Column E on both sheets have identical information; an alpha-numeric code formated xxxxx-xxxxx. What I am trying to make happen is when I click on Sheet 2, it filters column E to those codes that are currently being displayed on Sheet 1 through the activate event. Sheet 1 has a seperate column with project owners names on it that I use to filter the sheet by one name at a time and look at the information for all of the owners projects. One code per project. I am trying to get all the codes from Sheet 1 to store into an array but my loop exits when I get to the last cell in the range. I am trying to use a dynamic array that uses range(e65536).end(xlup).address as the upper limit of the array. Here is the code. Thanks for any help. Dim CCLimit As Integer Dim CCIndex() As String Dim MyRange As Object Dim EndPoint As Variant Dim Count As Long, i As Long Dim R As Object CCLimit = GetLimit() ReDim CCIndex(1 To CCLimit) i = 1 Count = 6 EndPoint = Worksheets("Sheet1").Range("E65536").End(xlUp).Add ress Set MyRange = Worksheets("Sheet1") MyAddress = MyRange.Range("E" & Count).Address Do If Sheets("Sheet1").Rows(Count).Hidden = False Then CCIndex(i) = Left(MyRange.Range("E" & Count).Value, 11) MsgBox CCIndex(i) i = i + 1 End If Count = Count + 1 MyAddress = MyRange.Range("E" & Count).Address Loop Until MyAddress = EndPoint All I need to know at this point is how to get the array to work. I tried to change Loop Until MyAddress = EndPoint to Loop Until MyAddress EndPoint but that did not work. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays problem in new filtering code
Thanks Rick. This is my first attempt at using an array. I have only been
programming spreadsheets for a while. I tried a few other ways around it but things ran slow or did not work. I knew more or less what an array was, but I wasn't sure the best way t use them. I know that Do...Loop looked funny, it as mostly because I was more concerned with making my array work right. If you noticed the GetLimit() function in there, that is another loop I used that looks just as bad as the one you saw. All it does is allow me to ReDim my array, I'm use you gathered that though. At one point both loops used For loops, but then all sense left me as I tried to get that array to fill up the way I wanted. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays problem in new filtering code
You can nest For..Next loops if needed. For example, consider this example
macro which fills in cells on the active sheet showing which loop variable is active at which time through the loops... Sub test() Dim X As Long Dim Y As Long For X = 1 To 5 For Y = 100 To 110 Range("A1").Offset(X - 1, Y - 100).Value = "X = " & X & ", Y = " & Y Next Next End Sub -- Rick (MVP - Excel) "Steve" wrote in message ... Thanks Rick. This is my first attempt at using an array. I have only been programming spreadsheets for a while. I tried a few other ways around it but things ran slow or did not work. I knew more or less what an array was, but I wasn't sure the best way t use them. I know that Do...Loop looked funny, it as mostly because I was more concerned with making my array work right. If you noticed the GetLimit() function in there, that is another loop I used that looks just as bad as the one you saw. All it does is allow me to ReDim my array, I'm use you gathered that though. At one point both loops used For loops, but then all sense left me as I tried to get that array to fill up the way I wanted. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Zip code filtering | Excel Discussion (Misc queries) | |||
How Populating Arrays/Range in VBA code | Excel Programming | |||
Arrays problem | Excel Programming | |||
VBA (arrays problem) | Excel Programming | |||
comparing elements from 2 arrays using VBA code | Excel Programming |