Getting a macro to run continuously

[Ferru123]

Bingo! It works!

Thank you so much for your patient help.

Now I know how to run a macro continuously I can experiment with more interesting stuff than copying a digit one from one cell into another!

Jeff

[Ferru123]

Hi

If I want to run a macro just once when I enter a new market, to erase whatever my macros did in the previous market - but for the other macro(s) to run continuously - how would I achieve this?

Thanks

Jeff

[Captain Sensible]

The easiest way is to simply check the market data in cell A1 against a stored variable - If the market (A1) is the same as the stored variable do nothing if it's different i.e. a new market run your sub routines to clear cells, set up distances etc anythng that only needs to run once.

Here's some code Gary posted years ago that you can tweak

Code: Select all

Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


If [A1].Value = MyMarket Then
' Your continuous sub rotuines can be entered or called fro here
GoTo Xit
Else
MyMarket = [A1].Value
'Call your subroutines that clear any old cells etc or just enter the code
Range("AC5:AD5").Value ="" ' we'll just clear data in cells C5 and D5 once when entering the maret for first time
End If




Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub


[Ferru123]

I can now get my spreadsheet to run continuously, but I keep getting Run Time Error 28.

I did a bit of Googling about how to overcome this, and I tried getting my macro to run every 5 seconds by changing my 'Sheet 1' code to the following:

Option Explicit

Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

If [A1].Value = MyMarket Then

Call TimerSet
Call NewProcedure1

GoTo Xit
Else
MyMarket = [A1].Value

Range("AF5:AF50").Value = ""
End If


Xit:

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

Sub TimerSet()
Application.OnTime Now + TimeValue("00:00:05"), "TimerSet"
End Sub



However, it still keeps crashing.

Any suggestions?

Thanks

Jeff

[Captain Sensible]

I think 28 means you're running out of memory space. I've no idea what your code is doing but it's most likely continually eating up memory, maybe you haven't declared some variables or your TimerSet routine is continually opening.

You need to include the following in your code as every change to the sheet is currently retriggering the worksheet change event. Just add it after your Static MyMarket As Variant line. What it does is exits the routine if we haven't received a price data refresh (they update 16 columns)

Code: Select all

    If Target.Columns.Count <> 16 Then Exit Sub

Also not sure why you're using Application.OnTime to set things off, it's just as easy to alter the refresh rate in Q2 . I'm not clued up with Application.OnTime so no idea if you need to close down instances of it running before opening extra ones.

[Ferru123]

Thank you.

I've changed my code to the following:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


If [A1].Value = MyMarket Then

Call NewProcedure1

GoTo Xit
Else
MyMarket = [A1].Value

Range("AF5:AF50").Value = ""
End If


Xit:

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub


However, I'm now getting Run Type Error 13 - type mismatch.

Is that due to the sheet code, or is it to do with the macros I'm running?

Thanks

Jeff

[Captain Sensible]

it'll be down to whatever's in NewProcedure1, seems like you're trying to compare mismatched data, when you use Option Explicit it expects you to define data types so if you're trying to compare a number against some text it'll throw up a mismatch error. So you need to avoid those situatins or create error trapping for if they do occur.

[Ferru123]

Thank you.

The code in NewProcedure1 is as follows, followed by the relevant Macros (which are straightforward copy and paste macros):

Sub NewProcedure1()

If Range("AE5").Value = 1 Then Call Macro1

If Range("AE6").Value = 1 Then Call Macro2

If Range("AE7").Value = 1 Then Call Macro3

If Range("AE8").Value = 1 Then Call Macro4

If Range("AE9").Value = 1 Then Call Macro5

If Range("AE10").Value = 1 Then Call Macro6

If Range("AE10").Value = 1 Then Call Macro6

End Sub

Sub reset()
Application.EnableEvents = True

End Sub

Any suggestions?

Thanks,

Jeff

[Captain Sensible]

You haven't said what problems you're having, if it's mismatch 13 it'll most likely be down to whatevers in your macro 1 etc

[Ferru123]

Hi

Macro1 is just a 'copy and paste values only' command:

Sub Macro1()

Range("AE5").Select
Selection.Copy
Range("AF5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub


Jeff

[Captain Sensible]

Looks like you've just recordd macros and then copied and pasted them, you'd be much better learning the VBA basics from the links Mark posted as copying cells is as simple as, Range("AE5").Value=Range("AF5").Value , I've no idea if your macros are releasing the areas it's selecting.

If you're simply checking AE5:AE10 for 1's then copying to AF5 something like this in Sub NewProcedure1() would replace all your macros, or even just siply stick the code to replace Call newprocedure

Code: Select all

Sub NewProcedure1()
For i =5 to 10
If Range("AE" & i).Value=1 then Range("AF" & i).Value=Range("AE" & i).Value
next i
end sub

[Ferru123]

Thank you.

The challenge I had was that I wanted to copy the value but not the formula of the source cell, so I recorded a macro that did that.

Jeff

[Captain Sensible]

No problem, with VBA there's always more than one way to do things. I'm sure most of the code I use could easily be shortened and made more efficient by someone more clued up with VBA than me.

With VBA there are so many RANGE properties you can use, there's a list here https://msdn.microsoft.com/en-us/librar ... 01006.aspx

So using Range("AE5").Value would just specify or get the value of the cell only, if we wanted to copy the formula only we could use

Range("AF5").Value = Range("AE5").Formula

It's worth taking the time to pick up the basics rather than recording and copy macros as your end code will be a lot more efficient and easier to read.

[Ferru123]

Thanks.

I've gone with the following, but am still getting a type mismatch:

Sub NewProcedure1()

If Range("ae5").Value = 1 Then Range("AF5").Value = Range("AE5").Formula

If Range("AE6").Value = 1 Then Range("AF6").Value = Range("AE6").Formula

If Range("AE7").Value = 1 Then Range("AF7").Value = Range("AE7").Formula

If Range("AE8").Value = 1 Then Range("AF8").Value = Range("AE8").Formula

If Range("AE9").Value = 1 Then Range("AF9").Value = Range("AE9").Formula

If Range("AE10").Value = 1 Then Range("AF10").Value = Range("AE10").Formula


End Sub

Sub reset()
Application.EnableEvents = True

End Sub


Could the solution be to upgrade to a later version of Excel? I am using 2007.

Jeff

[Captain Sensible]

2007 should be fine, are you sure it's that routine giving you the errors? And are you sure you want to copy the formulas now instead of the values?

Range("AF5").Value = Range("AE5").Formula

translates to the value in AF5 will now be equal to the formula in AE5, so if AE5 says Sum(A1:B1) AF5 will now also equal Sum(A1:B1)

Range("AF5").Value = Range("AE5").Value

translates to the value in AF5 will now be equal to the VALUE in AE5, so if AE5 says Sum(A1:B1) AF5 will now also equal the sum of the numbers not the formula.


Either way that code shouldn't be giving you mismatch errors, have you tried commenting out the Call proceedure line to ensure it is that section giving errors?