MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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