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

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

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

"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"

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

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

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

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

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

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

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