Select case with public variable???

I am trying to write a code that will look at the values in several worksheets and if the cell it looks at has the same value as the job number input by the user, I want to copy and paste the row into a worksheet called "Tracking Macro". I had the program running but the screen flashes as it runs so I decided to try altering it and use the screen updating = false line. However that meant I had to take out all the selection and active lines I used. Now the sub lookup will not run. Here is the start of the code...

Please Login or Register  to view this content.

That all runs ok, then I call the "lookup" sub and I get error message 438: "Object does not support this property or method." After some process of ellimination I believe it is that the computer is not recignizing "StLook" as a range. Help ! I am fairly new to VBA programming. Here is the "lookup" sub...

Please Login or Register  to view this content.

You use "screen updating = false" if you want to take the lazy/slow approach and select cells and worksheets, etc. In that case, you don't *need* to remove the selects, although you should.

You could use that method while you work out the better way by not selecting stuff.

Regards

Are you saying I should be able to use select without this happening?

I thought so. Unless I'm missing something, you should be able to sitch off screen updating, execute your code, and then switch it on again when you're finished to see the finished effect.

I stand to be corrected but that's one of the major time savers.

Note that it is usually called as:

Please Login or Register  to view this content.

If you had "Option Explicit" at the top of your code, you'd probably get Variable undefined for ScreenUpdating


Regards

No, you have "ScreenUpdating = False"

That's not the same

Regards

Do not use names for macros that have been reserve in VBA or Excel: 'format', 'lookup"
You don't need public variables to pass argument to a macro/function.
You don't need call to start a macro, just 'call it by it's name.
Use the reference of a range:

Please Login or Register  to view this content.

If you are new to VBA the best way to start is reading a basic book on VBA.
Like most other languages it has a certain structure & conventions. So start at the beginning.
After grasping the fundamentals it's much easier to understand the more complex stuff.

They're just snb's way of using variables. He's not a believer in dimming variables, and he likes to keep his code "concise" ... hence the strap line: Avoid using VBA-code you do not understand.

If you find it difficult to recognise a variable in VBA code after attending a week long course, you should get your money back


Regards

I use c00, c01, c02, etc. as variables.
If it's not strictly necessary I do not declare variables (that's a personal style).
At the same time I try to restrict the amount of variables to a minimum.
Most of the variables I use are consequently (because of not declaring) variant variables.
The errors you get are probably the result of 'option explicit'. If you remove that instruction, error messages will disappear.

I'm surprised you were not instructed in your reading/course not to use 'reserved names' in VBA or Excel.

Create a new workbook
Add a module
Copy and paste the following code
Select the workbook
Run the code

What do you see? It should end up on sheet3 with "test" in cell A60 with no sheet swaps or cell selection visible.

Please Login or Register  to view this content.

Regards

Must be your system. It doesn't do that on mine.

Regards