need help with VBA OuickPicks

[samhayer]

thanks I will get there I am sure thanks

[Captain Sensible]

Yep handling all the variables can be a bit much to get your head round to start with especially if you're not too used to coding.

When I started I used to write any variables directly to the worksheet rather than stick them in memory because it was easier and I could see at a glance what stage my bots were at. i.e. instead of having a variable currentMarket I'd simply dump the value into AA2 and reference that cell

Code: Select all

 if A1<>AA2 then .....

If things seem too complicated you can always go that route especially as you're already dumping data like balances to the sheet2 anyway.

[Captain Sensible]

If things seem too complicated you can always go that route especially as you're already dumping data like balances to the sheet2 anyway.

Cork has been delayed so quickly written a routine that won't need any variables so you can simply drop it into your orignal sheet if you can't get the hang of using Public variables. Either stick it in a sub routine along with your macros or simply stick it in the original code you posted on page 1 like so as that was working for you with all your other code.

Code: Select all

Option Explicit

Dim currentMarket As String

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count = 16 Then
If [A1] <> currentMarket Then
currentMarket = [A1]

logBalance
Call Macro2
Call Macro4
Call Macro5
End If
End If

''''''''''''''''''''''''''''''''''''''''''''
Application.EnableEvents = False
If Worksheets("Sheet1").Range("T1").Value = "Reload first market" Then
Worksheets("Sheet1").Range("T1").Value = Date & " QPL reloaded " & Time()
ElseIf Worksheets("Sheet1").Range("T1").Value = "Reload QPL" Then
Worksheets("Sheet1").Range("T1").Value = "Reload first market"
Worksheets("Sheet1").Range("Q2").Value = 5
ElseIf InStr(Worksheets("Sheet1").Range("T1").Value, Date) <> 1 Then
Worksheets("Sheet1").Range("T1").Value = "Reload QPL"
Worksheets("Sheet1").Range("Q2").Value = 3
End If
Application.EnableEvents = True
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

End Sub

Private Sub logBalance()
Application.EnableEvents = False
Dim r As Integer
r = 2
While Sheet2.Cells(r, 1) <> ""
r = r + 1
Wend
Sheet2.Cells(r, 1) = currentMarket
Sheet2.Cells(r, 2) = [I2]
Application.EnableEvents = True
End Sub

If you want to call it as a sub routine just replace the code section with Call refeshQPL and stick this with your macros inn the module

Code: Select all

Sub refreshQPL
Application.EnableEvents = False
If Worksheets("Sheet1").Range("T1").Value = "Reload first market" Then
Worksheets("Sheet1").Range("T1").Value = Date & " QPL reloaded " & Time()
ElseIf Worksheets("Sheet1").Range("T1").Value = "Reload QPL" Then
Worksheets("Sheet1").Range("T1").Value = "Reload first market"
Worksheets("Sheet1").Range("Q2").Value = 5
ElseIf InStr(Worksheets("Sheet1").Range("T1").Value, Date) <> 1 Then
Worksheets("Sheet1").Range("T1").Value = "Reload QPL"
Worksheets("Sheet1").Range("Q2").Value = 3
End If
Application.EnableEvents = True
End Sub



I do think they're are better ways to do it coding wise but it also needs to be something you can follow and works for you without taking ages and I've no idea if your macro are interferring also. If you can't figure it out using declared variables maybe give this a go for now. It'll output data to cell T1 so you can see it's working, even manually deleting data in T1 will set the routine running again.