Need Code Critique. Is there a Better or Faster Way?

[rdcnaz]

I am new to VB/Macros. I would like any experienced programmers to critique the below code. The code works fine, but is there a better (more proper) way to write it? Is there a different technique that would be faster?

I have not used Case Select before, but read that it is a better approach for longer If...Else code. This code will grow to about 50 selections. Is the Select Case any quicker?

This code basically searched column A of sheet 1 for a specific term. When term is identified, it copies the contents of the 5 cells to the right (Column B,C,D,E,F) one by one and pastes that data to specific cell on sheet2. The search is used on sheet1 because the record location changes regularly(imported data, no control over that).

Please Login or Register  to view this content.

[shg]

Without knowing what you're doing, I'd do something more like this:

Please Login or Register  to view this content.

And, ...

o Declare all variables appropriate to usage
o Don't Activate or Select unless necessary

There's a lengthy thread on this topic at http://www.excelforum.com/excel-prog...id-in-vba.html

[davegugg]

Does this work correctly?:

Please Login or Register  to view this content.

Should be more efficient with less selecting and activating of sheets and cells.

[rdcnaz]

Thank you shg. I will try your code suggestion, and read the thread link you provided. Like I said, I'm new to writing macros and know just enough to get myself lost. I know there are several ways to do the same thing, so I like to see and try the other ways people would do this, so I can pick it apart and learn from it. Thanks again for the quick response!

[rdcnaz]

Thank you davegugg. Your code is significantly faster than what I wrote. Like you and shg pointed out, I had way to many occurrences of activating sheets and cells. I had no idea that I could nest For...Next commands inside If's and other For...Next. I realize that I am writing step by step, activating everything every time I do something.

I guess with time and a lot of writing I'll improve. Thanks for the quick response.

[rdcnaz]

shg,

Quick question. What does the following do?

Please Login or Register  to view this content.

Your code works extremely fast as well, but copies sheet1 every other cell (Column B,D,F) instead of each of the next 5 cells (B,C,D,E,F). Again, I have not used Case Select, so I am trying to use this piece of code to understand it. Thanks in Advance...

[shg]

Well ... you see what it does. As I said, I don't know what you're trying to do, and didn't want to take the time to reverse engineer your code to figure it out.

If you have a specific question, please start a new thread with an appropriate title and post a workbook.