Finding ranges in an array, cut and paste also.

[ChuckM]

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

 

[Alan Beban]

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

Alan Beban

 

[Tom Ogilvy]

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

 

[Alan Beban]

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

 

[Chuck Mryglot]

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

 

[Alan Beban]

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

Which one?

Alan Beban

 

[Alan Beban]

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

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

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