MS Excel Macro Question.?

Question

I need to write a MACRO in Excel 2003 SP2. Can you do it for me? If you do, I'll send you £50 Postal Order and I am serious.

Task:
A window appears with 2 inputs required:
1. Project Number (6 digit string) (Example 123456)
2. Task Number (3 digit string) (example 789)
User enters these digits and clicks OK.
I then need the macro to look in to a source workbook always called in C:\My Documents\FSA\Monthly OPA Extract\Working.xls worksheet "Project CC" Rows 4 to end of data.
If it finds a match for Project Code in Column A and the contents of Column E start with the Task Number, it should copy each row from column A to column R and paste values of cells in to the receiving worksheet "Labour Data" in the open workbook stating at Cell G3.

The process is repeated until all rows in the Working.xls worksheet have been read. Each time it comes across a new instance of "Project Code = 123456" in column A and "Task Code = 789" in colum E it copies the row to the next free row.

Answer 1

I'm not really a fan of Neil's code. It has a number of possible error problems and looks sloppily and hastily written.

One problem that I see is regarding the number of rows. Excel 2003 has 65,536 rows. Neil specified less than that number. If the user ever went over that number they could have an error occur or the macro may not check for anything past 64,000. A good programmer would check it against the number of total rows in the worksheet by using a line of code such as:

ActiveSheet.Rows.Count

That would give them the total rows in the worksheet to know how long the worksheet is. Another thing a good programmer would do is limit it to just the used range. There is no need to cycle 64,000 times when you don't have to. That takes extra time. If that worksheet has only 100 rows of data the macro should only evaluate the used range so that the macro is much faster.

A third thing that Neil does is that he works with the actual cells instead of working with the data in arrays. To make the data work much faster the data should be transfered to an array. Then after it is transfered to an array all of the comparing should be done. The difference between the two methods can be quite a bit. If you do it by using cell comparisons like Neil did, it could take a couple of minutes to do all of the evaluations. If you do it with array comparisons, the same process could take less than a second to run.

The array comparisons make the macro a little more complex to write and right now I dont' have the time to write out the code, but you are welcome to email me and I would be more than willing to help you develop a code that should work properly and fast.

Answer 2

You do not say what you want the macro to do, so I am wondering if a simple conditional formatting or an IF function will do instead of using a macro.