Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment an Array question
I'm searching up a column looking for a particular value.
When found, I need to add a range offset from that cell to an array. I find that the following code is overwriting varr(1) each time....so the array never builds correctly i = i + 1 If IsEmpty(varr(1)) Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If Why is the array not building correctly, please? Regards |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment an Array question
If can do two things
if the first element is empty, it writes the range value in the first element. otherwise, it increases the upperbound of the array by one and writes it in the new element. I am not sure what role "i" plays, but you increment that and don't use it. If StartRw is not being incremented, then you would write the same value in every element. I find that the following code is overwriting varr(1) each time Not unless .Range("B" & StartRw) is empty and you are placing an empty value in varr(1) -- Regards, Tom Ogilvy "S G Booth" wrote in message ... I'm searching up a column looking for a particular value. When found, I need to add a range offset from that cell to an array. I find that the following code is overwriting varr(1) each time....so the array never builds correctly i = i + 1 If IsEmpty(varr(1)) Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If Why is the array not building correctly, please? Regards |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment an Array question
SG,
You have to increment the Array. I started from scratch and wrote something I think is close to what you want? See if this is any help... '------------------------------------------------------- Sub AnotherTest() Dim i As Long Dim N As Long Dim X As Long Dim SearchRange As Excel.Range Dim varr() As Double Set SearchRange = Range(Cells(10, 1), Cells(Rows.Count, 1).End(xlUp)) N = SearchRange.Count ReDim varr(1 To N) For i = N To 1 Step -1 If SearchRange(i).Value = "Now Cough" Then X = X + 1 varr(X) = SearchRange(i).Offset(0, 1).Value End If Next 'i ReDim Preserve varr(1 To X) 'Just an example - as could run out of columns Range(Cells(5, 4), Cells(5, 3 + X)).Value = varr() Set SearchRange = Nothing End Sub '------------------------------------------------------------ Regards, Jim Cone San Francisco, USA "S G Booth" wrote in message ... I'm searching up a column looking for a particular value. When found, I need to add a range offset from that cell to an array. I find that the following code is overwriting varr(1) each time....so the array never builds correctly i = i + 1 If IsEmpty(varr(1)) Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If Why is the array not building correctly, please? Regards |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment an Array question
You have to increment the Array.
It does increment the array first. Explain where you think the existing code does not work. Maybe my explanation was flawed. Since this is part of a bigger routine, starting form scratch is probably not an option. -- Regards, Tom Ogilvy "Jim Cone" wrote in message ... SG, You have to increment the Array. I started from scratch and wrote something I think is close to what you want? See if this is any help... '------------------------------------------------------- Sub AnotherTest() Dim i As Long Dim N As Long Dim X As Long Dim SearchRange As Excel.Range Dim varr() As Double Set SearchRange = Range(Cells(10, 1), Cells(Rows.Count, 1).End(xlUp)) N = SearchRange.Count ReDim varr(1 To N) For i = N To 1 Step -1 If SearchRange(i).Value = "Now Cough" Then X = X + 1 varr(X) = SearchRange(i).Offset(0, 1).Value End If Next 'i ReDim Preserve varr(1 To X) 'Just an example - as could run out of columns Range(Cells(5, 4), Cells(5, 3 + X)).Value = varr() Set SearchRange = Nothing End Sub '------------------------------------------------------------ Regards, Jim Cone San Francisco, USA "S G Booth" wrote in message ... I'm searching up a column looking for a particular value. When found, I need to add a range offset from that cell to an array. I find that the following code is overwriting varr(1) each time....so the array never builds correctly i = i + 1 If IsEmpty(varr(1)) Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If Why is the array not building correctly, please? Regards |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment an Array question
Have seen both your replies and Jim's. Thanks to you both. It is a segment
in a bigger series of routines. I still cannot see the error(s). Will try to create a simple set of data, with the relevent code routine and post back. Regards and thanks. "Tom Ogilvy" wrote in message ... If can do two things if the first element is empty, it writes the range value in the first element. otherwise, it increases the upperbound of the array by one and writes it in the new element. I am not sure what role "i" plays, but you increment that and don't use it. If StartRw is not being incremented, then you would write the same value in every element. I find that the following code is overwriting varr(1) each time Not unless .Range("B" & StartRw) is empty and you are placing an empty value in varr(1) -- Regards, Tom Ogilvy "S G Booth" wrote in message ... I'm searching up a column looking for a particular value. When found, I need to add a range offset from that cell to an array. I find that the following code is overwriting varr(1) each time....so the array never builds correctly i = i + 1 If IsEmpty(varr(1)) Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If Why is the array not building correctly, please? Regards |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment an Array question
Tom,
My remarks were addressed to SG. If the Range assigned to the Varr(1) is blank, then it will be evaluated as "IsEmpty" and will be overwritten on the next loop. Regards, Jim Cone "Tom Ogilvy" wrote in message ... You have to increment the Array. It does increment the array first. Explain where you think the existing code does not work. Maybe my explanation was flawed. Since this is part of a bigger routine, starting form scratch is probably not an option. Regards, Tom Ogilvy "Jim Cone" wrote in message ... SG, You have to increment the Array. I started from scratch and wrote something I think is close to what you want? See if this is any help... '------------------------------------------------------- Sub AnotherTest() Dim i As Long Dim N As Long Dim X As Long Dim SearchRange As Excel.Range Dim varr() As Double Set SearchRange = Range(Cells(10, 1), Cells(Rows.Count, 1).End(xlUp)) N = SearchRange.Count ReDim varr(1 To N) For i = N To 1 Step -1 If SearchRange(i).Value = "Now Cough" Then X = X + 1 varr(X) = SearchRange(i).Offset(0, 1).Value End If Next 'i ReDim Preserve varr(1 To X) 'Just an example - as could run out of columns Range(Cells(5, 4), Cells(5, 3 + X)).Value = varr() Set SearchRange = Nothing End Sub '------------------------------------------------------------ Regards, Jim Cone San Francisco, USA "S G Booth" wrote in message ... I'm searching up a column looking for a particular value. When found, I need to add a range offset from that cell to an array. I find that the following code is overwriting varr(1) each time....so the array never builds correctly i = i + 1 If IsEmpty(varr(1)) Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If Why is the array not building correctly, please? Regards |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment an Array question
"S G Booth" wrote in message
... Have seen both your replies and Jim's. Thanks to you both. It is a segment in a bigger series of routines. I still cannot see the error(s). .. I find that the following code is overwriting varr(1) each time....so the array never builds correctly i = i + 1 If IsEmpty(varr(1)) Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If why do you need "isempty()" at any moment simply use: redim preserve varr(ubound(varr)+1) varr(ubound)=... when starting, use: dim varr() which is equal to "zero" length array, so, with first adding you should create your varr(1) ... anyway, be aware of "option base 0/1" |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment an Array question
Guess my explanation was right on. Thanks.
-- Regards, Tom Ogilvy "Jim Cone" wrote in message ... Tom, My remarks were addressed to SG. If the Range assigned to the Varr(1) is blank, then it will be evaluated as "IsEmpty" and will be overwritten on the next loop. Regards, Jim Cone "Tom Ogilvy" wrote in message ... You have to increment the Array. It does increment the array first. Explain where you think the existing code does not work. Maybe my explanation was flawed. Since this is part of a bigger routine, starting form scratch is probably not an option. Regards, Tom Ogilvy "Jim Cone" wrote in message ... SG, You have to increment the Array. I started from scratch and wrote something I think is close to what you want? See if this is any help... '------------------------------------------------------- Sub AnotherTest() Dim i As Long Dim N As Long Dim X As Long Dim SearchRange As Excel.Range Dim varr() As Double Set SearchRange = Range(Cells(10, 1), Cells(Rows.Count, 1).End(xlUp)) N = SearchRange.Count ReDim varr(1 To N) For i = N To 1 Step -1 If SearchRange(i).Value = "Now Cough" Then X = X + 1 varr(X) = SearchRange(i).Offset(0, 1).Value End If Next 'i ReDim Preserve varr(1 To X) 'Just an example - as could run out of columns Range(Cells(5, 4), Cells(5, 3 + X)).Value = varr() Set SearchRange = Nothing End Sub '------------------------------------------------------------ Regards, Jim Cone San Francisco, USA "S G Booth" wrote in message ... I'm searching up a column looking for a particular value. When found, I need to add a range offset from that cell to an array. I find that the following code is overwriting varr(1) each time....so the array never builds correctly i = i + 1 If IsEmpty(varr(1)) Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If Why is the array not building correctly, please? Regards |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment an Array question
didn't I write the original quite a while ago? Such as
http://groups.google.co.uk/groups?hl...TK2MSF TNGP12 or is it derived from something I wrote? Has it not worked for a long time or has it always been problematic? -- Regards, Tom Ogilvy "S G Booth" wrote in message ... Have seen both your replies and Jim's. Thanks to you both. It is a segment in a bigger series of routines. I still cannot see the error(s). Will try to create a simple set of data, with the relevent code routine and post back. Regards and thanks. "Tom Ogilvy" wrote in message ... If can do two things if the first element is empty, it writes the range value in the first element. otherwise, it increases the upperbound of the array by one and writes it in the new element. I am not sure what role "i" plays, but you increment that and don't use it. If StartRw is not being incremented, then you would write the same value in every element. I find that the following code is overwriting varr(1) each time Not unless .Range("B" & StartRw) is empty and you are placing an empty value in varr(1) -- Regards, Tom Ogilvy "S G Booth" wrote in message ... I'm searching up a column looking for a particular value. When found, I need to add a range offset from that cell to an array. I find that the following code is overwriting varr(1) each time....so the array never builds correctly i = i + 1 If IsEmpty(varr(1)) Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If Why is the array not building correctly, please? Regards |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment an Array question
by the way, my explanation was flawed. Since it is a set command, once
used, varr(1) can not be empty and will not be overwritten (at least with the code you show - if you empty it somewhere else, then there is no accounting for that). as I recall, you were trying to identify pagebreaks, so apparently code that you don't show prevents assignments as you expect. the code you show would work as designed. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... didn't I write the original quite a while ago? Such as http://groups.google.co.uk/groups?hl...TK2MSF TNGP12 or is it derived from something I wrote? Has it not worked for a long time or has it always been problematic? -- Regards, Tom Ogilvy "S G Booth" wrote in message ... Have seen both your replies and Jim's. Thanks to you both. It is a segment in a bigger series of routines. I still cannot see the error(s). Will try to create a simple set of data, with the relevent code routine and post back. Regards and thanks. "Tom Ogilvy" wrote in message ... If can do two things if the first element is empty, it writes the range value in the first element. otherwise, it increases the upperbound of the array by one and writes it in the new element. I am not sure what role "i" plays, but you increment that and don't use it. If StartRw is not being incremented, then you would write the same value in every element. I find that the following code is overwriting varr(1) each time Not unless .Range("B" & StartRw) is empty and you are placing an empty value in varr(1) -- Regards, Tom Ogilvy "S G Booth" wrote in message ... I'm searching up a column looking for a particular value. When found, I need to add a range offset from that cell to an array. I find that the following code is overwriting varr(1) each time....so the array never builds correctly i = i + 1 If IsEmpty(varr(1)) Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If Why is the array not building correctly, please? Regards |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment an Array question
That was indeed your code, thanks, and it is still working fine for me.
This is a similar situation (re pages), but where the user has used a routine to insert a page into the sheet.....I now need to revise the Collection page(s) at the end of the sheet. I'm trying to find where the first Collection page starts, and if there are subsequent Collection pages, how many. As before, each page starts with "Item" in Col1. If it's the initial Collection page, then an offset from "Item" Offset(1, 1).End(xlDown).Value should return "COLLECTION". If it's a subsequent Collection then it'll be "COLLECTION (Cont.)". Any other value should mean a normal data page. In my test sheet I had "Item" in A1, A55, A114, A173. I had "COLLECTION" in B58, and "COLLECTION (Cont.)" in B117 and B176. To mark the end of the sheet, I used "£" in F231. Sub TestArray() Dim C As Range, TargetCell As String Dim myRng As Range, £Col As Integer Dim LastRw As Long, ws As Worksheet Dim StartRw As Long Dim varr() £Col = 6 Set ws = ActiveSheet With ws Set myRng = (.Cells(65536, £Col).End(xlUp) _ .Offset(0, -(£Col - 1))) myRng.Select LastRw = myRng.Row ReDim varr(1 To 1) Do 'Until TargetCell = .Columns(1).Find(What:="Item", _ After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Select If ActiveCell.Offset(1, 1).End(xlDown).Value _ = "COLLECTION" Then 'it should mean only one Collection page StartRw = ActiveCell.Offset(1, 1) _ .End(xlDown).Row + 2 If IsEmpty(varr(1)) Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If ElseIf ActiveCell.Offset(1, 1).End(xlDown).Value = _ "COLLECTION (Cont.)" Then 'it should mean multiple Collections StartRw = ActiveCell.Offset(1, 1) _ .End(xlDown).Row + 2 If IsEmpty(varr(1)) Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If ElseIf ActiveCell.Row = 1 Then Exit Do End If Loop End With End Sub For me it seems to overwrite varr(1) rather than increment the array.....and I don't know why. Any help much appreciated. Regards. "Tom Ogilvy" wrote in message ... by the way, my explanation was flawed. Since it is a set command, once used, varr(1) can not be empty and will not be overwritten (at least with the code you show - if you empty it somewhere else, then there is no accounting for that). as I recall, you were trying to identify pagebreaks, so apparently code that you don't show prevents assignments as you expect. the code you show would work as designed. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... didn't I write the original quite a while ago? Such as http://groups.google.co.uk/groups?hl...TK2MSF TNGP12 or is it derived from something I wrote? Has it not worked for a long time or has it always been problematic? -- Regards, Tom Ogilvy "S G Booth" wrote in message ... Have seen both your replies and Jim's. Thanks to you both. It is a segment in a bigger series of routines. I still cannot see the error(s). Will try to create a simple set of data, with the relevent code routine and post back. Regards and thanks. "Tom Ogilvy" wrote in message ... If can do two things if the first element is empty, it writes the range value in the first element. otherwise, it increases the upperbound of the array by one and writes it in the new element. I am not sure what role "i" plays, but you increment that and don't use it. If StartRw is not being incremented, then you would write the same value in every element. I find that the following code is overwriting varr(1) each time Not unless .Range("B" & StartRw) is empty and you are placing an empty value in varr(1) -- Regards, Tom Ogilvy "S G Booth" wrote in message ... I'm searching up a column looking for a particular value. When found, I need to add a range offset from that cell to an array. I find that the following code is overwriting varr(1) each time....so the array never builds correctly i = i + 1 If IsEmpty(varr(1)) Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If Why is the array not building correctly, please? Regards |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment an Array question
Actually, I think this is an anomaly in the use of isempty.
Isempty checks if a variant variable has been initialized. The purpose of the check in this code is to see whether varr(1) has been initialized and if not assign the value to varr(1). If it has, then raise the upperbound and assign it to the last item in the array. Unfortunately, since varr(1) is being set as a reference to a range, if it does hold a range, then isempty checks the situation for the range (is the range empty). So for a range object, the correct test would be if varr(1) is nothing then however, if varr(1) has actually never been initialized (which was the real purpose of the test) then this test will fail since only an object can be checked with "is nothing" To overcome this, I think you should alter your code to: i = i + 1 If TypeName(varr(1)) = "Empty" Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If You can ignore my 2nd response that if a range is assigned it can't evaluate to empty - I went crazy for a minute <g. Hopefully this will fix your problem. If not, post back. -- Regards, Tom Ogilvy "S G Booth" wrote in message ... That was indeed your code, thanks, and it is still working fine for me. This is a similar situation (re pages), but where the user has used a routine to insert a page into the sheet.....I now need to revise the Collection page(s) at the end of the sheet. I'm trying to find where the first Collection page starts, and if there are subsequent Collection pages, how many. As before, each page starts with "Item" in Col1. If it's the initial Collection page, then an offset from "Item" Offset(1, 1).End(xlDown).Value should return "COLLECTION". If it's a subsequent Collection then it'll be "COLLECTION (Cont.)". Any other value should mean a normal data page. In my test sheet I had "Item" in A1, A55, A114, A173. I had "COLLECTION" in B58, and "COLLECTION (Cont.)" in B117 and B176. To mark the end of the sheet, I used "£" in F231. Sub TestArray() Dim C As Range, TargetCell As String Dim myRng As Range, £Col As Integer Dim LastRw As Long, ws As Worksheet Dim StartRw As Long Dim varr() £Col = 6 Set ws = ActiveSheet With ws Set myRng = (.Cells(65536, £Col).End(xlUp) _ .Offset(0, -(£Col - 1))) myRng.Select LastRw = myRng.Row ReDim varr(1 To 1) Do 'Until TargetCell = .Columns(1).Find(What:="Item", _ After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Select If ActiveCell.Offset(1, 1).End(xlDown).Value _ = "COLLECTION" Then 'it should mean only one Collection page StartRw = ActiveCell.Offset(1, 1) _ .End(xlDown).Row + 2 If IsEmpty(varr(1)) Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If ElseIf ActiveCell.Offset(1, 1).End(xlDown).Value = _ "COLLECTION (Cont.)" Then 'it should mean multiple Collections StartRw = ActiveCell.Offset(1, 1) _ .End(xlDown).Row + 2 If IsEmpty(varr(1)) Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If ElseIf ActiveCell.Row = 1 Then Exit Do End If Loop End With End Sub For me it seems to overwrite varr(1) rather than increment the array.....and I don't know why. Any help much appreciated. Regards. "Tom Ogilvy" wrote in message ... by the way, my explanation was flawed. Since it is a set command, once used, varr(1) can not be empty and will not be overwritten (at least with the code you show - if you empty it somewhere else, then there is no accounting for that). as I recall, you were trying to identify pagebreaks, so apparently code that you don't show prevents assignments as you expect. the code you show would work as designed. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... didn't I write the original quite a while ago? Such as http://groups.google.co.uk/groups?hl...TK2MSF TNGP12 or is it derived from something I wrote? Has it not worked for a long time or has it always been problematic? -- Regards, Tom Ogilvy "S G Booth" wrote in message ... Have seen both your replies and Jim's. Thanks to you both. It is a segment in a bigger series of routines. I still cannot see the error(s). Will try to create a simple set of data, with the relevent code routine and post back. Regards and thanks. "Tom Ogilvy" wrote in message ... If can do two things if the first element is empty, it writes the range value in the first element. otherwise, it increases the upperbound of the array by one and writes it in the new element. I am not sure what role "i" plays, but you increment that and don't use it. If StartRw is not being incremented, then you would write the same value in every element. I find that the following code is overwriting varr(1) each time Not unless .Range("B" & StartRw) is empty and you are placing an empty value in varr(1) -- Regards, Tom Ogilvy "S G Booth" wrote in message ... I'm searching up a column looking for a particular value. When found, I need to add a range offset from that cell to an array. I find that the following code is overwriting varr(1) each time....so the array never builds correctly i = i + 1 If IsEmpty(varr(1)) Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If Why is the array not building correctly, please? Regards |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment an Array question
Yes, seems to work fine now. Have only run it on the
Sub Test() code so far. Thanks also for the explanation. I've sampled up the sheet to find the Collection pages because they're at the end of each sheet...seemed quicker. However, the array elements will now be in the reverse order to their sequence in the sheet (varr(1) will now be the very last Collection page). Since I will wish to loop through the array elements, can I do it in reverse order...something like a For Each and Step -1 statement? Regards and thanks. "Tom Ogilvy" wrote in message ... Actually, I think this is an anomaly in the use of isempty. Isempty checks if a variant variable has been initialized. The purpose of the check in this code is to see whether varr(1) has been initialized and if not assign the value to varr(1). If it has, then raise the upperbound and assign it to the last item in the array. Unfortunately, since varr(1) is being set as a reference to a range, if it does hold a range, then isempty checks the situation for the range (is the range empty). So for a range object, the correct test would be if varr(1) is nothing then however, if varr(1) has actually never been initialized (which was the real purpose of the test) then this test will fail since only an object can be checked with "is nothing" To overcome this, I think you should alter your code to: i = i + 1 If TypeName(varr(1)) = "Empty" Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If You can ignore my 2nd response that if a range is assigned it can't evaluate to empty - I went crazy for a minute <g. Hopefully this will fix your problem. If not, post back. -- Regards, Tom Ogilvy "S G Booth" wrote in message ... That was indeed your code, thanks, and it is still working fine for me. This is a similar situation (re pages), but where the user has used a routine to insert a page into the sheet.....I now need to revise the Collection page(s) at the end of the sheet. I'm trying to find where the first Collection page starts, and if there are subsequent Collection pages, how many. As before, each page starts with "Item" in Col1. If it's the initial Collection page, then an offset from "Item" Offset(1, 1).End(xlDown).Value should return "COLLECTION". If it's a subsequent Collection then it'll be "COLLECTION (Cont.)". Any other value should mean a normal data page. In my test sheet I had "Item" in A1, A55, A114, A173. I had "COLLECTION" in B58, and "COLLECTION (Cont.)" in B117 and B176. To mark the end of the sheet, I used "£" in F231. Sub TestArray() Dim C As Range, TargetCell As String Dim myRng As Range, £Col As Integer Dim LastRw As Long, ws As Worksheet Dim StartRw As Long Dim varr() £Col = 6 Set ws = ActiveSheet With ws Set myRng = (.Cells(65536, £Col).End(xlUp) _ .Offset(0, -(£Col - 1))) myRng.Select LastRw = myRng.Row ReDim varr(1 To 1) Do 'Until TargetCell = .Columns(1).Find(What:="Item", _ After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Select If ActiveCell.Offset(1, 1).End(xlDown).Value _ = "COLLECTION" Then 'it should mean only one Collection page StartRw = ActiveCell.Offset(1, 1) _ .End(xlDown).Row + 2 If IsEmpty(varr(1)) Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If ElseIf ActiveCell.Offset(1, 1).End(xlDown).Value = _ "COLLECTION (Cont.)" Then 'it should mean multiple Collections StartRw = ActiveCell.Offset(1, 1) _ .End(xlDown).Row + 2 If IsEmpty(varr(1)) Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If ElseIf ActiveCell.Row = 1 Then Exit Do End If Loop End With End Sub For me it seems to overwrite varr(1) rather than increment the array.....and I don't know why. Any help much appreciated. Regards. "Tom Ogilvy" wrote in message ... by the way, my explanation was flawed. Since it is a set command, once used, varr(1) can not be empty and will not be overwritten (at least with the code you show - if you empty it somewhere else, then there is no accounting for that). as I recall, you were trying to identify pagebreaks, so apparently code that you don't show prevents assignments as you expect. the code you show would work as designed. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... didn't I write the original quite a while ago? Such as http://groups.google.co.uk/groups?hl...TK2MSF TNGP12 or is it derived from something I wrote? Has it not worked for a long time or has it always been problematic? -- Regards, Tom Ogilvy "S G Booth" wrote in message ... Have seen both your replies and Jim's. Thanks to you both. It is a segment in a bigger series of routines. I still cannot see the error(s). Will try to create a simple set of data, with the relevent code routine and post back. Regards and thanks. "Tom Ogilvy" wrote in message ... If can do two things if the first element is empty, it writes the range value in the first element. otherwise, it increases the upperbound of the array by one and writes it in the new element. I am not sure what role "i" plays, but you increment that and don't use it. If StartRw is not being incremented, then you would write the same value in every element. I find that the following code is overwriting varr(1) each time Not unless .Range("B" & StartRw) is empty and you are placing an empty value in varr(1) -- Regards, Tom Ogilvy "S G Booth" wrote in message ... I'm searching up a column looking for a particular value. When found, I need to add a range offset from that cell to an array. I find that the following code is overwriting varr(1) each time....so the array never builds correctly i = i + 1 If IsEmpty(varr(1)) Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If Why is the array not building correctly, please? Regards |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment an Array question
for i = ubound(varr) to lbound(varr) step - 1
msgbox i & ": " & varr(i) Next i -- Regards, Tom Ogilvy "S G Booth" wrote in message ... Yes, seems to work fine now. Have only run it on the Sub Test() code so far. Thanks also for the explanation. I've sampled up the sheet to find the Collection pages because they're at the end of each sheet...seemed quicker. However, the array elements will now be in the reverse order to their sequence in the sheet (varr(1) will now be the very last Collection page). Since I will wish to loop through the array elements, can I do it in reverse order...something like a For Each and Step -1 statement? Regards and thanks. "Tom Ogilvy" wrote in message ... Actually, I think this is an anomaly in the use of isempty. Isempty checks if a variant variable has been initialized. The purpose of the check in this code is to see whether varr(1) has been initialized and if not assign the value to varr(1). If it has, then raise the upperbound and assign it to the last item in the array. Unfortunately, since varr(1) is being set as a reference to a range, if it does hold a range, then isempty checks the situation for the range (is the range empty). So for a range object, the correct test would be if varr(1) is nothing then however, if varr(1) has actually never been initialized (which was the real purpose of the test) then this test will fail since only an object can be checked with "is nothing" To overcome this, I think you should alter your code to: i = i + 1 If TypeName(varr(1)) = "Empty" Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If You can ignore my 2nd response that if a range is assigned it can't evaluate to empty - I went crazy for a minute <g. Hopefully this will fix your problem. If not, post back. -- Regards, Tom Ogilvy "S G Booth" wrote in message ... That was indeed your code, thanks, and it is still working fine for me. This is a similar situation (re pages), but where the user has used a routine to insert a page into the sheet.....I now need to revise the Collection page(s) at the end of the sheet. I'm trying to find where the first Collection page starts, and if there are subsequent Collection pages, how many. As before, each page starts with "Item" in Col1. If it's the initial Collection page, then an offset from "Item" Offset(1, 1).End(xlDown).Value should return "COLLECTION". If it's a subsequent Collection then it'll be "COLLECTION (Cont.)". Any other value should mean a normal data page. In my test sheet I had "Item" in A1, A55, A114, A173. I had "COLLECTION" in B58, and "COLLECTION (Cont.)" in B117 and B176. To mark the end of the sheet, I used "£" in F231. Sub TestArray() Dim C As Range, TargetCell As String Dim myRng As Range, £Col As Integer Dim LastRw As Long, ws As Worksheet Dim StartRw As Long Dim varr() £Col = 6 Set ws = ActiveSheet With ws Set myRng = (.Cells(65536, £Col).End(xlUp) _ .Offset(0, -(£Col - 1))) myRng.Select LastRw = myRng.Row ReDim varr(1 To 1) Do 'Until TargetCell = .Columns(1).Find(What:="Item", _ After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Select If ActiveCell.Offset(1, 1).End(xlDown).Value _ = "COLLECTION" Then 'it should mean only one Collection page StartRw = ActiveCell.Offset(1, 1) _ .End(xlDown).Row + 2 If IsEmpty(varr(1)) Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If ElseIf ActiveCell.Offset(1, 1).End(xlDown).Value = _ "COLLECTION (Cont.)" Then 'it should mean multiple Collections StartRw = ActiveCell.Offset(1, 1) _ .End(xlDown).Row + 2 If IsEmpty(varr(1)) Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If ElseIf ActiveCell.Row = 1 Then Exit Do End If Loop End With End Sub For me it seems to overwrite varr(1) rather than increment the array.....and I don't know why. Any help much appreciated. Regards. "Tom Ogilvy" wrote in message ... by the way, my explanation was flawed. Since it is a set command, once used, varr(1) can not be empty and will not be overwritten (at least with the code you show - if you empty it somewhere else, then there is no accounting for that). as I recall, you were trying to identify pagebreaks, so apparently code that you don't show prevents assignments as you expect. the code you show would work as designed. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... didn't I write the original quite a while ago? Such as http://groups.google.co.uk/groups?hl...TK2MSF TNGP12 or is it derived from something I wrote? Has it not worked for a long time or has it always been problematic? -- Regards, Tom Ogilvy "S G Booth" wrote in message ... Have seen both your replies and Jim's. Thanks to you both. It is a segment in a bigger series of routines. I still cannot see the error(s). Will try to create a simple set of data, with the relevent code routine and post back. Regards and thanks. "Tom Ogilvy" wrote in message ... If can do two things if the first element is empty, it writes the range value in the first element. otherwise, it increases the upperbound of the array by one and writes it in the new element. I am not sure what role "i" plays, but you increment that and don't use it. If StartRw is not being incremented, then you would write the same value in every element. I find that the following code is overwriting varr(1) each time Not unless .Range("B" & StartRw) is empty and you are placing an empty value in varr(1) -- Regards, Tom Ogilvy "S G Booth" wrote in message ... I'm searching up a column looking for a particular value. When found, I need to add a range offset from that cell to an array. I find that the following code is overwriting varr(1) each time....so the array never builds correctly i = i + 1 If IsEmpty(varr(1)) Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If Why is the array not building correctly, please? Regards |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment an Array question
Many thanks for all the help.
Regards. "Tom Ogilvy" wrote in message ... for i = ubound(varr) to lbound(varr) step - 1 msgbox i & ": " & varr(i) Next i -- Regards, Tom Ogilvy "S G Booth" wrote in message ... Yes, seems to work fine now. Have only run it on the Sub Test() code so far. Thanks also for the explanation. I've sampled up the sheet to find the Collection pages because they're at the end of each sheet...seemed quicker. However, the array elements will now be in the reverse order to their sequence in the sheet (varr(1) will now be the very last Collection page). Since I will wish to loop through the array elements, can I do it in reverse order...something like a For Each and Step -1 statement? Regards and thanks. "Tom Ogilvy" wrote in message ... Actually, I think this is an anomaly in the use of isempty. Isempty checks if a variant variable has been initialized. The purpose of the check in this code is to see whether varr(1) has been initialized and if not assign the value to varr(1). If it has, then raise the upperbound and assign it to the last item in the array. Unfortunately, since varr(1) is being set as a reference to a range, if it does hold a range, then isempty checks the situation for the range (is the range empty). So for a range object, the correct test would be if varr(1) is nothing then however, if varr(1) has actually never been initialized (which was the real purpose of the test) then this test will fail since only an object can be checked with "is nothing" To overcome this, I think you should alter your code to: i = i + 1 If TypeName(varr(1)) = "Empty" Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If You can ignore my 2nd response that if a range is assigned it can't evaluate to empty - I went crazy for a minute <g. Hopefully this will fix your problem. If not, post back. -- Regards, Tom Ogilvy "S G Booth" wrote in message ... That was indeed your code, thanks, and it is still working fine for me. This is a similar situation (re pages), but where the user has used a routine to insert a page into the sheet.....I now need to revise the Collection page(s) at the end of the sheet. I'm trying to find where the first Collection page starts, and if there are subsequent Collection pages, how many. As before, each page starts with "Item" in Col1. If it's the initial Collection page, then an offset from "Item" Offset(1, 1).End(xlDown).Value should return "COLLECTION". If it's a subsequent Collection then it'll be "COLLECTION (Cont.)". Any other value should mean a normal data page. In my test sheet I had "Item" in A1, A55, A114, A173. I had "COLLECTION" in B58, and "COLLECTION (Cont.)" in B117 and B176. To mark the end of the sheet, I used "£" in F231. Sub TestArray() Dim C As Range, TargetCell As String Dim myRng As Range, £Col As Integer Dim LastRw As Long, ws As Worksheet Dim StartRw As Long Dim varr() £Col = 6 Set ws = ActiveSheet With ws Set myRng = (.Cells(65536, £Col).End(xlUp) _ .Offset(0, -(£Col - 1))) myRng.Select LastRw = myRng.Row ReDim varr(1 To 1) Do 'Until TargetCell = .Columns(1).Find(What:="Item", _ After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Select If ActiveCell.Offset(1, 1).End(xlDown).Value _ = "COLLECTION" Then 'it should mean only one Collection page StartRw = ActiveCell.Offset(1, 1) _ .End(xlDown).Row + 2 If IsEmpty(varr(1)) Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If ElseIf ActiveCell.Offset(1, 1).End(xlDown).Value = _ "COLLECTION (Cont.)" Then 'it should mean multiple Collections StartRw = ActiveCell.Offset(1, 1) _ .End(xlDown).Row + 2 If IsEmpty(varr(1)) Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If ElseIf ActiveCell.Row = 1 Then Exit Do End If Loop End With End Sub For me it seems to overwrite varr(1) rather than increment the array.....and I don't know why. Any help much appreciated. Regards. "Tom Ogilvy" wrote in message ... by the way, my explanation was flawed. Since it is a set command, once used, varr(1) can not be empty and will not be overwritten (at least with the code you show - if you empty it somewhere else, then there is no accounting for that). as I recall, you were trying to identify pagebreaks, so apparently code that you don't show prevents assignments as you expect. the code you show would work as designed. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... didn't I write the original quite a while ago? Such as http://groups.google.co.uk/groups?hl...TK2MSF TNGP12 or is it derived from something I wrote? Has it not worked for a long time or has it always been problematic? -- Regards, Tom Ogilvy "S G Booth" wrote in message ... Have seen both your replies and Jim's. Thanks to you both. It is a segment in a bigger series of routines. I still cannot see the error(s). Will try to create a simple set of data, with the relevent code routine and post back. Regards and thanks. "Tom Ogilvy" wrote in message ... If can do two things if the first element is empty, it writes the range value in the first element. otherwise, it increases the upperbound of the array by one and writes it in the new element. I am not sure what role "i" plays, but you increment that and don't use it. If StartRw is not being incremented, then you would write the same value in every element. I find that the following code is overwriting varr(1) each time Not unless .Range("B" & StartRw) is empty and you are placing an empty value in varr(1) -- Regards, Tom Ogilvy "S G Booth" wrote in message ... I'm searching up a column looking for a particular value. When found, I need to add a range offset from that cell to an array. I find that the following code is overwriting varr(1) each time....so the array never builds correctly i = i + 1 If IsEmpty(varr(1)) Then Set varr(1) = .Range("B" & StartRw) Else ReDim Preserve varr(1 To UBound(varr) + 1) Set varr(UBound(varr)) = .Range("B" & StartRw) End If Why is the array not building correctly, please? Regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Question | Excel Discussion (Misc queries) | |||
array question | Excel Worksheet Functions | |||
Array Question | Excel Discussion (Misc queries) | |||
Increment array for debits and credits | Excel Programming | |||
Increment array range | Excel Programming |