I'd appreciate some help in re-writing the following bit of
lookup/copy/paste code so that it runs faster. Typically, StartRow will have
a value of 10, and EndRow could be 4000 or 5000.

For r = StartRow To EndRow
If Application.IsNA(Cells(r, ChanCode_Col)) = False Then
SelChanCode = Cells(r, ChanCode_Col)
RegSht.Select
SelFactorRow = Application.Match(SelChanCode, Range("b:b"), 0)
Range(Cells(SelFactorRow, 6), Cells(SelFactorRow, 18)).Copy
Sheets("Report").Select
Cells(r, 23).Select
ActiveSheet.Paste
End If
Next

Thanks very much for any help and suggestions.


Colin

Change this segment:

Range(Cells(SelFactorRow, 6), Cells(SelFactorRow, 18)).Copy
Sheets("Report").Select
Cells(r, 23).Select
ActiveSheet.Paste

To this:

Range(Cells(SelFactorRow, 6), Cells(SelFactorRow, 18)).Copy _
Sheets("Report").Cells(r, 23)

You can put it all on one line by deleting the subscript (underscore) and
just leave a space between Copy and Sheets.

"Colin Macleod" wrote in message
...
I'd appreciate some help in re-writing the following bit of
lookup/copy/paste code so that it runs faster. Typically, StartRow will
have a value of 10, and EndRow could be 4000 or 5000.

For r = StartRow To EndRow
If Application.IsNA(Cells(r, ChanCode_Col)) = False Then
SelChanCode = Cells(r, ChanCode_Col)
RegSht.Select
SelFactorRow = Application.Match(SelChanCode, Range("b:b"), 0)
Range(Cells(SelFactorRow, 6), Cells(SelFactorRow, 18)).Copy
Sheets("Report").Select
Cells(r, 23).Select
ActiveSheet.Paste
End If
Next

Thanks very much for any help and suggestions.


Colin

Range(Cells(SelFactorRow, 6), Cells(SelFactorRow, 18)).Copy _
Sheets("Report").Cells(r, 23)

This is a little more compact way to write the above line of code...

Cells(SelFactorRow, 6).Resize(SelFactorRow, 13).Copy Sheets("Report").Cells(r, 23)

--
Rick (MVP - Excel)



"JLGWhiz" wrote in message ...
Change this segment:

Range(Cells(SelFactorRow, 6), Cells(SelFactorRow, 18)).Copy
Sheets("Report").Select
Cells(r, 23).Select
ActiveSheet.Paste

To this:

Range(Cells(SelFactorRow, 6), Cells(SelFactorRow, 18)).Copy _
Sheets("Report").Cells(r, 23)

You can put it all on one line by deleting the subscript (underscore) and
just leave a space between Copy and Sheets.

"Colin Macleod" wrote in message
...
I'd appreciate some help in re-writing the following bit of
lookup/copy/paste code so that it runs faster. Typically, StartRow will
have a value of 10, and EndRow could be 4000 or 5000.

For r = StartRow To EndRow
If Application.IsNA(Cells(r, ChanCode_Col)) = False Then
SelChanCode = Cells(r, ChanCode_Col)
RegSht.Select
SelFactorRow = Application.Match(SelChanCode, Range("b:b"), 0)
Range(Cells(SelFactorRow, 6), Cells(SelFactorRow, 18)).Copy
Sheets("Report").Select
Cells(r, 23).Select
ActiveSheet.Paste
End If
Next

Thanks very much for any help and suggestions.


Colin

Thanks for your help on this.


"Colin Macleod" wrote in message
...
I'd appreciate some help in re-writing the following bit of
lookup/copy/paste code so that it runs faster. Typically, StartRow will
have a value of 10, and EndRow could be 4000 or 5000.

For r = StartRow To EndRow
If Application.IsNA(Cells(r, ChanCode_Col)) = False Then
SelChanCode = Cells(r, ChanCode_Col)
RegSht.Select
SelFactorRow = Application.Match(SelChanCode, Range("b:b"), 0)
Range(Cells(SelFactorRow, 6), Cells(SelFactorRow, 18)).Copy
Sheets("Report").Select
Cells(r, 23).Select
ActiveSheet.Paste
End If
Next

Thanks very much for any help and suggestions.


Colin