Help my slow looping process
Posted by David on October 10, 2000 12:54 AM
Can anyone help me speed this up. I am searching through a list of over 500 lines for matching serial numbers. My is statement is a lot longer than this one posted but that doesn;t matter. The point is when I find one on worksheet list I need to copy the first 11 columns to worksheet main. If there are 400 matches then it loops 400 * 11 times. I would like to skip the i = 1 to 11 loop somehow.
something like a copy paste but am haveing trouble.
Any of you excel gods want to give me a pointer or two?
For x = 3 To Worksheets("List").Range("A3").End(xlDown).Row + 1
If Worksheets("Main").Cells(2, 2)= (Worksheets("List").Cells(x, 1) Then
For I = 1 To 11
Worksheets("Main").Cells(rowcount, I) = Worksheets("List").Cells(x, I)
Next I
rowcount = rowcount + 1
end if
next x
Posted by David on October 10, 2000 12:56 AM
clarification
something like this
Worksheets("Main").Range(Cells(rowcount, 1), Cells(rowcount, 11)) = Worksheets("List").Range(Cells(x, 1), Cells(x, 11))
Posted by David on October 10, 2000 1:02 AM
one solution
figured it out
range1 = "A" & rowcount & ":J" & rowcount
range2 = "A" & x & ":J" & x
Worksheets("Main").Range(range1) = Worksheets("List").Range(range2)
any better idea's
Posted by David on October 10, 2000 1:04 AM
Nope that doesn't work
got my columns and rows confused
Posted by David on October 10, 2000 1:10 AM
Re: Nope that doesn't work
Wait fixed it again
range1 = "A" & rowcount & ":J" & rowcount
range2 = "A" & x & ":J" & x
Worksheets("Main").Range(range1).Value = Worksheets("List").Range(range2).Value
it was .value i needed. the rows were fine.
Posted by DoughBoy on October 10, 2000 1:20 AM
Is this guy lonely or something?
Posted by Jim Knicely on October 10, 2000 5:47 AM
Try using the range copy method:
Range(Worksheets("list").Cells(x, 1), Worksheets("list").Cells(x, 11)).Copy _
Range(Worksheets("main").Cells(RowCount, 1), Worksheets("main").Cells(RowCount, 11))
.. it works a lot faster than looping ...
