Using the routine that Hans suggested I have built code to count ticks between numbers except for .4 and .9 which will always be excluded.
Code: Select all
Function CountTicks(varStart, varEnd)
Dim I As Long, J As Long
Dim intCurWhle As Integer, intPrevWhle As Integer
Dim dblPrevFrc As Double, dblCurFrc As Double, dblBuyP As Double, dblSellP As Double
Dim R As Long, P As Double, p1 As Double
R = ActiveSheet.UsedRange.Row
'varStart = 5
'varEnd = 6.5
If Cells(9, 4) = "Buy To Close" Or Cells(9, 4) = "Sell To Close" Then
P = InStr(Cells(9, 3), "'")
intCurWhle = Left(Cells(9, 3), P - 1)
dblCurFrc = Mid(Cells(9, 3), P + 1)
End If
If Cells(10, 4) = "Buy To Open" Or Cells(10, 4) = "Sell To Open" Then
P = InStr(Cells(10, 3), "'")
intPrevWhle = Left(Cells(10, 3), P - 1)
dblPrevFrc = Mid(Cells(10, 3), P + 1)
End If
If Cells(9, 12) = 46 Or Cells(9, 12) = 47 Then
dblBuyP = intCurWhle + dblCurFrc / 32
Else
dblSellP = intCurWhle + dblCurFrc / 32
End If
If Cells(10, 12) = 46 Or Cells(10, 12) = 47 Then
dblBuyP = intPrevWhle + dblPrevFrc / 32
Else
dblSellP = intPrevWhle + dblPrevFrc / 32
End If
varStart = dblPrevFrc
varEnd = Abs(intPrevWhle - intCurWhle) * 256 + dblCurFrc
If varStart <= varEnd Then
For J = 10 * varStart + 1 To 10 * varEnd
If J Mod 10 <> 4 And J Mod 10 <> 9 Then
CountTicks = CountTicks + 1
End If
Next J
Else
For J = 10 * varStart - 1 To 10 * varEnd Step -1
If J Mod 10 <> 4 And J Mod 10 <> 9 Then
CountTicks = CountTicks + 1
End If
Next J
End If
If dblSellP > dblBuyP Then
Cells(9, 15) = Abs(Cells(9, 2)) * CountTicks * 7.8125 - Abs(Cells(9, 8)) - Abs(Cells(10, 8))
Else
Cells(9, 15) = -Abs(Cells(9, 2)) * CountTicks * 7.8125 - Abs(Cells(9, 8)) - Abs(Cells(10, 8))
End If
End Function