Help with macros

[Peter1973]

I am just starting to experiment with macros i can get the macro to work and assign a shortcut key and tollbar button, however i have seen worksheets with action buttons in which when hit performs the macro, how do i put one of these in a worksheet.????????????

 

[Smitty]

There are two ways:

The easiest is using a Forms button. When drawn you'll be asked to assign a macro.

The second is to use an ActiveX control from the Control Toolbox, which requires you to code each control individually, but you can call general subs from them.

I.E.

Private Sub CommandButton1_Click()
Call Macro1
End Sub

Hope that helps,

Smitty

 

[Peter1973]

I am going to sound really thick here that was great got that sorted and working but want to take it a step further ie i can get my macro to run commands but what is the best way to incorperate formulas ie if macro1 is run then A1 = ??? eg when macro run A1=B1+C1, i have tried doing it but it seems to run the formulas before i run the macro.

Basically i want a macro that only calculates my info when promted.

 

[Smitty]

Can you post the code you have now and indicate what you're trying to do?

You can always set Calculation to manual at the beginning of your code and set it to automatic at the end if that's what you're after.

Smitty

 

[Peter1973]

I want to set up 4 macros which perform different tasks with the same data. ie:

Macro1

Looks in cell A1 then searches rows B1 to AA1 for a match to A1 then in the corresponding column counts the total number of "H".

The other macros are going to be the same but searching different columns so i will just adaot the first macro.

 

[Smitty]

What's "H"?

If it's text that you're searching for in that range wouldn't COUNTIF work?

Smitty

 

[Peter1973]

Yes ican do it with a countif however im experimenting with macros that use formulas so just want to use a simple formula in a macro with an action button so i can get the hang of them.

 

[Smitty]

There are two ways to do that. Actually record the formula; get a formula working the way you want, then record a macro and hit F2-->Enter on that cell to record it.

Or you can use Applicaiton.WorksheetFunction. The helpfile details it pretty well.

Smitty

 

[Peter1973]

I have a sheet with various data in colums i want a macro that will do three things,

eg
columnA columnB ColumnC

Paul 17:00 H
John 21:00 w
Steve 14:00 S
Ian 05:00 W

I want to reset the sheet so all cells with a "W" in column C are at the top then sort these cells be column A in alphabetical order then sort by column B in time order.

Does this make sense?

 

[Smitty]

Why not just record it:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> Macro1()
    Range("A1:C5").Sort Key1:=Range("C2"), Order1:=xlDescending, Key2:=Range( _
        "A2"), Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
        xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
        DataOption3:=xlSortNormal
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

That'll give you the base code.

Smitty