Formula TIMEVALUE

[Sussexguy44]

Hiya,

Im trying to enter an excel formula with a view to ensuring that I get a 1 value if the matched volume is £1000 or more at eight minutes before the off.. Once the 1 or 0 is in place I dont want it to change. So far I have IF(AND($D$2>TIMEVALUE(00:08:00),$B£3>1000)1,0). It does not work just now. Any help would be welcome.

Cheers


Dave

[Captain Sensible]

You'll need to do it in VBA as the formula will continually calculate every time D2 changes, plus I'm not sure if it's a typo or error but $B£3>1000 isn't valid probably should be $B$3

[Captain Sensible]

In VBA something as simple as this should work fine. You'd just need to clear AA1 each time you enter a new market which is easy enougfh to code in if you don't already have code that sets up variables when entering a new market.

Code: Select all


With Target.Parent

If .Range("AA1").Value ="" And .Range("D2").Value >= (TimeValue("00:08:00")) And .Range("B3").Value > 1000 Then
.Range("AA1").Value = 1
End if

End With

[Sussexguy44]

Cheers,

Im using .Range(AH5).ClearContents just below the other ClearContents macros. Does this look OK?

[Captain Sensible]

As long as any clearcontents code comes before, that should be fine.

[Sussexguy44]

Hiya, My VBA is below. I have added the additions as discussed but I am struggling to get it to function. I think it is a small error. The additions made are .Range(AH5).ClearContents and If .Range("AH5").Value = "" And .Range("D2").Value >= (TimeValue("00:08:00")) And .Range("B3").Value > 1000 Then .Range("AH5").Value = 1

Any help would be welcome

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count <> 16 Then Exit Sub
Static MyMarket As String
Dim lastRow As Long
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With Target.Parent

lastRow = .Range("Z" & Rows.Count).End(xlUp).Row

If .Range("A1").Value <> MyMarket Then
MyMarket = .Range("A1").Value
.Range("AA5:AA" & lastRow).ClearContents
.Range("AM5:AM" & lastRow).ClearContents
.Range(AH5).ClearContents
End If


If WorksheetFunction.IsText(.Range("D2").Value) = False And (IsEmpty(Range("AA5").Value) Or WorksheetFunction.IsText(.Range("AA5").Value)) Then
If .Range("D2").Value <= TimeValue("00:08:00") Then
.Range("AA5:AA" & lastRow).Value = .Range("Z5:Z" & lastRow).Value
.Range("AM5:AM" & lastRow).Value = .Range("F5:F" & lastRow).Value
End If

With Target.Parent

If .Range("AH5").Value = "" And .Range("D2").Value >= (TimeValue("00:08:00")) And .Range("B3").Value > 1000 Then
.Range("AH5").Value = 1
End If

End With
End If




End With


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True


End Sub

[brumbie]

.Range(AH5).ClearContents should be .Range("AH5").ClearContents


Surely the runtime error would have pointed you to this?

I don't know what you have in Column Z so I changed it to another column to test and it seems to work apart from that 1 error.

[Captain Sensible]

Surely the runtime error would have pointed you to this?

I'd agree with brumbie, most errors with VBA will point you to where the error is occuring.

Only only thing I'd mention is you don't need to add With Target.Parent again as your code is already within a With Target.Parent loop.

[Sussexguy44]

Hi Guys,

Thanks for your input. It works well now. I didn't get a Runtime error however any VBA that does work is not shown on the Excel tab when you try to link to it with Gruss. The error makes it (and other worksheets) disappear.

I have found the useful Next-Market-Trigger spreadsheet which I want to incorporate the VBA into my current workbooks. I have tried to copy and paste the VBA over onto my worksheets however it does not work. Sorry for my naivety however is there a simple solution?

[Captain Sensible]

No idea what the next market trigger spreadsheet is or does, without seeing the code and how/where you're adding it it's hard to second guess.

Are you getting any VBA error messages or just not doing what you expect? Sometimes it's worth using the debug.print options to check things are firing as you want.

[brumbie]

If it's a Worksheet_Change code then you can only have one Worksheet_Change event procedure code per worksheet, so you need to combine them into one.

[Sussexguy44]

Cheers,
So can I simply cut and paste the VBA as noted below and paste it in front of the VBA I have in place. I am using the Change worksheet function you mentioned.

Dim currentMarket As String, marketSelected As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
Dim timeFromStart As Date, beforeStart As Boolean, secondsFromStart As Integer
If Target.Columns.Count = 16 Then
Application.EnableEvents = False
If Left([D2], 1) = "-" Then
timeFromStart = Mid([D2], 2)
beforeStart = False
Else
timeFromStart = [D2]
beforeStart = True
End If
secondsFromStart = (Hour(timeFromStart) * 3600) + (Minute(timeFromStart) * 60) + Second(timeFromStart)
If Not beforeStart Then secondsFromStart = -secondsFromStart
If [A1] <> currentMarket Then marketSelected = False
currentMarket = [A1]
If secondsFromStart <= -600 And Not marketSelected Then
marketSelected = True
[Q2] = -1
End If
Application.EnableEvents = True
End If
End Sub

[Captain Sensible]

Like brumbie said you can't have two worksheet_change events so you'd have to combine the code rather than posting one complete worksheet_change snippet on top of another.

It's generally better practice to break things up into sub routines, stick them in a module then call them when needed. Makes your code a lot easier to read and tweak.

[Sussexguy44]

So I have module 1 and module 2. Module 2 is the original. When I click Macros it shows Macro 2 as a name that I used. I am looking to name both different parts of the VBA and combine the. Does this make sense. I am struggling a little.

[Captain Sensible]

Not really sure what you mean by all the macros and modules. Might be worth you taking a bt of time to understand how BA and VBA work together.

For the VBA code to run when it's linked to a spreadsheet most people use a Worksheet_Change event. An event could simply be considered something that happens when the worksheet is changed i.e BA has sent a load of updated prices to the worksheet, worksheet_change then fires off any code contained with in it.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

'ALL OUR CODE RUNS WITHIN HERE BECAUSE SOMETHING HAS CHANGED WITHIN THE WORKSHEET THAT BA SENDS DATA TO

End Sub

The Worksheet_Change coding needs to be placed under the sheet your data is sent to not in a module as they'd be nothing to fire it off from there. You place sub routines in modules and CALL them from the Worksheet_Change routine like so

In a module

Code: Select all

Sub test()

MsgBox "hello"

End Sub

In Sheet1

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

CALL test

End Sub

Screenshot 2022-01-08 174251.png (27.06 KiB) Viewed 12274 times