If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Combining two macros
Hi
I have two macros that i would like to combine by adding Sub Every5() to the start of Sub AUTOINTRADAY()so that it runs every five minutes based on the conditions set out below(running 20 seconds after each five minute period.) Must be doing something wrong as i can't get it to work. Further down the line i will be hooking Excel up to a real time data feed for financial data.I would need to run a macro at five minute intervals. However i can't run it exactly on the 5 minute intervals eg:-18:55:00,19:00:00,19:05:00,becuse i need to run it maybe 20 seconds after the five minute intervals to allow the data to be posted in the data feeds database,eg:-18:55:20,19:00:20,19:05:20 Sub Every5() 'start the timer on a multiple of 5 minutes ts = #1:00:00 PM# dur = 5 * 60 'seconds 5 minutes seq = 1 Do DoEvents this = (Timer - ts - 20) Mod dur If this last Then MsgBox "FIRE" End If last = this Loop End Sub Sub AUTOINTRADAY() ' ' AUTOINTRADAY Macro ' Macro recorded 27/05/2004 by ADE ' ' Sheets("IMPORT").Select Range("A1:F551").Select Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Key2:=Range("B1") _ , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ :=xlSortNormal Sheets("IMPORT").Select Range("A2:F459").Select ActiveWindow.SmallScroll Down:=-108 Selection.Copy Range("B3:F730").Select Application.CutCopyMode = False Selection.Copy Sheets("FILTER").Select ActiveWindow.SmallScroll ToRight:=-1 Range("A3").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("G5").Select Sheets("FILTER").Select Range("A2:E383").ADVANCEDFILTER Action:=xlFilterCopy, CriteriaRange:=Range( _ "Z2:AE3"), CopyToRange:=Range("Z4"), Unique:=False ActiveWindow.SmallScroll ToRight:=14 Range("A2:E383").ADVANCEDFILTER Action:=xlFilterCopy, CriteriaRange:=Range( _ "AF2:AK3"), CopyToRange:=Range("AF4"), Unique:=False ActiveWindow.SmallScroll ToRight:=18 Range("A2:E383").ADVANCEDFILTER Action:=xlFilterCopy, CriteriaRange:=Range( _ "AL2:AQ3"), CopyToRange:=Range("AL4"), Unique:=False ActiveWindow.SmallScroll ToRight:=-8 ActiveWindow.SmallScroll Down:=-6 Range("A3:E239").Select Selection.Copy Sheets("GBP INTRA").Select ActiveWindow.SmallScroll Down:=-9 Range("A3").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False Range("G36").Select Sheets("FILTER").Select If [B2] [C5] And [B3] [C7] Or _ [B2] [C5] And [B3] [C7] Then xcount = 1 For xcount = 1 To 5 Beep newHour = Hour(Now()) newMinute = Minute(Now()) newSecond = Second(Now()) + 1 waitTime = TimeSerial(newHour, newMinute, newSecond) Application.Wait waitTime Next End If End Sub Thanks for any ideas Ade --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
Combining two macros
ADE,
Why not just call the second macro from the first? You could also look at OnTime, where you can specify the time to run, such as 18:55:20, etc., which will help overcome the time of the macro. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "ADE2 " wrote in message ... Hi I have two macros that i would like to combine by adding Sub Every5() to the start of Sub AUTOINTRADAY()so that it runs every five minutes based on the conditions set out below(running 20 seconds after each five minute period.) Must be doing something wrong as i can't get it to work. Further down the line i will be hooking Excel up to a real time data feed for financial data.I would need to run a macro at five minute intervals. However i can't run it exactly on the 5 minute intervals eg:-18:55:00,19:00:00,19:05:00,becuse i need to run it maybe 20 seconds after the five minute intervals to allow the data to be posted in the data feeds database,eg:-18:55:20,19:00:20,19:05:20 Sub Every5() 'start the timer on a multiple of 5 minutes ts = #1:00:00 PM# dur = 5 * 60 'seconds 5 minutes seq = 1 Do DoEvents this = (Timer - ts - 20) Mod dur If this last Then MsgBox "FIRE" End If last = this Loop End Sub Sub AUTOINTRADAY() ' ' AUTOINTRADAY Macro ' Macro recorded 27/05/2004 by ADE ' ' Sheets("IMPORT").Select Range("A1:F551").Select Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Key2:=Range("B1") _ , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ :=xlSortNormal Sheets("IMPORT").Select Range("A2:F459").Select ActiveWindow.SmallScroll Down:=-108 Selection.Copy Range("B3:F730").Select Application.CutCopyMode = False Selection.Copy Sheets("FILTER").Select ActiveWindow.SmallScroll ToRight:=-1 Range("A3").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("G5").Select Sheets("FILTER").Select Range("A2:E383").ADVANCEDFILTER Action:=xlFilterCopy, CriteriaRange:=Range( _ "Z2:AE3"), CopyToRange:=Range("Z4"), Unique:=False ActiveWindow.SmallScroll ToRight:=14 Range("A2:E383").ADVANCEDFILTER Action:=xlFilterCopy, CriteriaRange:=Range( _ "AF2:AK3"), CopyToRange:=Range("AF4"), Unique:=False ActiveWindow.SmallScroll ToRight:=18 Range("A2:E383").ADVANCEDFILTER Action:=xlFilterCopy, CriteriaRange:=Range( _ "AL2:AQ3"), CopyToRange:=Range("AL4"), Unique:=False ActiveWindow.SmallScroll ToRight:=-8 ActiveWindow.SmallScroll Down:=-6 Range("A3:E239").Select Selection.Copy Sheets("GBP INTRA").Select ActiveWindow.SmallScroll Down:=-9 Range("A3").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False Range("G36").Select Sheets("FILTER").Select If [B2] [C5] And [B3] [C7] Or _ [B2] [C5] And [B3] [C7] Then xcount = 1 For xcount = 1 To 5 Beep newHour = Hour(Now()) newMinute = Minute(Now()) newSecond = Second(Now()) + 1 waitTime = TimeSerial(newHour, newMinute, newSecond) Application.Wait waitTime Next End If End Sub Thanks for any ideas Ade --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|