What about macros 2

[paulomanuel]

I started to develop macros in EXCELL spreedsheets for particular aplications and soon realized that they are tricky.
I learned that you must execute macros locating (the cursor) in the sheet it is supposed to start, otherwise you woul watch it perform the operation over the wrong selection, for example.
What else is there about macros to watch out (apart from the virus issue)?

 

[Qshake]

Check out "Writing Excel Macros" from O'Reilly publishing.

 

[tigrek]

MACRO PERFORMING ON ACTIVE WORKSHEET

This is the default when you do not specify the kind of xls file or the workbook or the worksheet on which the macro is to operate.

If it causes problems, you may put a message box into the macro asking for varification of the active sheet.

You can even make the macro to check the xls file name or the sheet name before going on.

But as I said, for most operations, it is just great to have the macro run on the active workbook.

Compares to the Print tool that asks no questions - a bit annoying at first but very practical later.

 

[Mala]

When you write a XL macro to do something, there are several basic things you must take care of including the sheet for which it is supposed to run:
To cite one -
If the macro is intended to work on a range and the selection is an object, it'll screech to a halt with a suitable msg if the VBA code refers to the 'Selection' object. In such cases, its a good idea to incorporate in the macro, code to identify the type of selection before the main body of the code runs.. use typename

For example, exit the procedure without doing anything
Sub XYZ()
If typename(selection)<>&quot;Range&quot; then Exit Sub '
Other statements...
End Sub

OR Display a helpful msg

Sub XYZ()
If typename(selection)<>&quot;Range&quot; then msgbox(&quot;Select a range and try again&quot
Other statements...
End Sub

 

[smk424]

Can somebody let me know please how can I pick a particulr value from a range against a particular value. For instance If I am using from Menue, Data> Validation> Pick from List, and I have 2 colums in the list having particular value against a particular vriable, how can I put value against a particular variable in my required cell.
An example is :
Pipe Actual
Nominal Dia Inside Dia
2 2.067
2 1/2 2.469
3 3.068
I want to pick 3 and want the value 3.068 in my required cell. I shall be thankful for the help anybody can give me.

 

[Qshake]

You should check out VLOOKUP or HLOOKUP in the help menu.

 

[yakpol]

Use Lookup function

=LOOKUP(C1,A1:A3,B1:B3)