Can anyone give some help or guidance?
I have an array...fixed at 8 columns wide, but n rows deep. I need to
search down one of the columns for some known data ( in the m'th row)
and then make a copy of the array from the top row to the m'th onto
another sheet.

I'll appreciate any advice.
Thanks
Chuck

Do you mean you have an array of values in an array, or an array of
values in a range?

Alan Beban

ChuckM wrote:
Can anyone give some help or guidance?
I have an array...fixed at 8 columns wide, but n rows deep. I need to
search down one of the columns for some known data ( in the m'th row)
and then make a copy of the array from the top row to the m'th onto
another sheet.

I'll appreciate any advice.
Thanks
Chuck

You mean a vba array?

Dim myarray(1 to 200,1 to 8) as Long
Dim myarray()

for i = 1 to 2000
if myarray(i,3) = "target" then
mrow = i
exit for
end if
Next
Redim myarray1(1 to mrow, 1 to 8)
for i = 1 to mrow
for j = 1 to 8
myarray1(i,j) = myarray(i,j)
Next
Next

--
regards,
Tom Ogilvy



"ChuckM" wrote in message
m...
Can anyone give some help or guidance?
I have an array...fixed at 8 columns wide, but n rows deep. I need to
search down one of the columns for some known data ( in the m'th row)
and then make a copy of the array from the top row to the m'th onto
another sheet.

I'll appreciate any advice.
Thanks
Chuck

This seems kind of garbled. It's hard to follow what's supposed to refer
to myarray and what's supposed to refer to myarray1. In the first two
lines both are declared as myarray. What is myarray supposed to be
loaded with? What's the significance of "column 3 in myarray; what if
target is in some other column?

Alan Beban

Tom Ogilvy wrote:
You mean a vba array?

Dim myarray(1 to 200,1 to 8) as Long
Dim myarray()

for i = 1 to 2000
if myarray(i,3) = "target" then
mrow = i
exit for
end if
Next
Redim myarray1(1 to mrow, 1 to 8)
for i = 1 to mrow
for j = 1 to 8
myarray1(i,j) = myarray(i,j)
Next
Next

Hi Alan.
I have 8 columns of data...with a heading at the top of each
column...e.g. row 1 is headings and data starts in row 2. The data is
sorted. I need to look down one of the columns until I find a
particular integer value. Then I need to copy everything from row 2 down
to the row in which I found the integer I was looking for....call it row
n .....and past the 8 columns (with headings) from row 1 to row m onto a
new sheet.
chuck


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Chuck Mryglot wrote:

I need to look down one of the columns . . . .

Which one?

Alan Beban
Hi Alan.
I have 8 columns of data...with a heading at the top of each
column...e.g. row 1 is headings and data starts in row 2. The data is
sorted. I need to look down one of the columns until I find a
particular integer value. Then I need to copy everything from row 2 down
to the row in which I found the integer I was looking for....call it row
n .....and past the 8 columns (with headings) from row 1 to row m onto a
new sheet.
chuck


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I assumed your data is in A2:H100, and that you were looking for 32 in
Column 2:

Sub testIt()
n = 100
Set rng = Sheets("Sheet2").Range("a2:H" & n)
m = rng.Columns(2).Find(32).Row
Worksheets.Add
Sheets("Sheet2").Range("a2:h" & m).Copy ActiveSheet.Range("A2")
End Sub

Alan Beban

Chuck Mryglot wrote:
Hi Alan.
I have 8 columns of data...with a heading at the top of each
column...e.g. row 1 is headings and data starts in row 2. The data is
sorted. I need to look down one of the columns until I find a
particular integer value. Then I need to copy everything from row 2 down
to the row in which I found the integer I was looking for....call it row
n .....and past the 8 columns (with headings) from row 1 to row m onto a
new sheet.
chuck


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Wow, you caught me with a typo again.

Dim myarray(1 to 200,1 to 8) as Long
Dim myarray1()

Thanks for point that out, but it doesn't look like what the OP needed
anyway.

--
Regards,
Tom Ogilvy



"Alan Beban" wrote in message
...
This seems kind of garbled. It's hard to follow what's supposed to refer
to myarray and what's supposed to refer to myarray1. In the first two
lines both are declared as myarray. What is myarray supposed to be
loaded with? What's the significance of "column 3 in myarray; what if
target is in some other column?

Alan Beban

Tom Ogilvy wrote:
You mean a vba array?

Dim myarray(1 to 200,1 to 8) as Long
Dim myarray()

for i = 1 to 2000
if myarray(i,3) = "target" then
mrow = i
exit for
end if
Next
Redim myarray1(1 to mrow, 1 to 8)
for i = 1 to mrow
for j = 1 to 8
myarray1(i,j) = myarray(i,j)
Next
Next

Just some thoughts:


I don't see where the heading is copied as specified by the OP.
Might want to change

Sheets("Sheet2").Range("a2:h" & m).Copy ActiveSheet.Range("A2")

to
Sheets("Sheet2").Range("a1:h" & m).Copy ActiveSheet.Range("A1")



IF he is looking for 32 and it isn't found in the data it would cause an
error? The error isn't handled gracefully and would just puke all over the
screen.


If he has option explict at the top of the module, your code gives me an
error. Several in fact. (same with mine).


Sub testIt()
Dim n as Long
Dim m as Long
Dim rng as Range

n = 100
Set rng = Sheets("Sheet2").Range("a2:H" & n).Find(32)
if not rng is nothing then
m = rng.Row
Worksheets.Add
Sheets("Sheet2").Range("a1:h" & m).Copy ActiveSheet.Range("A1")
End if
End Sub



--
Regards,
Tom Ogilvy







"Alan Beban" wrote in message
...
I assumed your data is in A2:H100, and that you were looking for 32 in
Column 2:

Sub testIt()
n = 100
Set rng = Sheets("Sheet2").Range("a2:H" & n)
m = rng.Columns(2).Find(32).Row
Worksheets.Add
Sheets("Sheet2").Range("a2:h" & m).Copy ActiveSheet.Range("A2")
End Sub

Alan Beban

Chuck Mryglot wrote:
Hi Alan.
I have 8 columns of data...with a heading at the top of each
column...e.g. row 1 is headings and data starts in row 2. The data is
sorted. I need to look down one of the columns until I find a
particular integer value. Then I need to copy everything from row 2 down
to the row in which I found the integer I was looking for....call it row
n .....and past the 8 columns (with headings) from row 1 to row m onto a
new sheet.
chuck


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!