Solved

Scoring

Posted on 2014-02-13
26
105 Views
Last Modified: 2014-02-17
Sir gowflow,

Kindly make this Sub (Analyse) more friendly.
Sub Analyse()
Dim WS As Worksheet
Dim MaxRow As Long
Dim cCell As Range
Dim Min As Long, Max As Long, I As Long, J As Long, K As Long
Dim factor As String
Dim Col As String

Set WS = ThisWorkbook.ActiveSheet

WS.Range("B22") = WS.Range("H33")
WS.Range("B23") = WS.Range("I33")
WS.Range("B27") = WS.Range("B33")
WS.Range("B28") = WS.Range("G33")
WS.Range("C29") = WS.Range("A33")


'---> High
If WS.Range("C29") = "High" Then
    WS.Range("B49") = WS.Range("B49") + 1
    WS.Range("D49") = WS.Range("D49") + 1
    WS.Range("F49") = WS.Range("F49") + 1
    WS.Range("H49") = WS.Range("H49") + 1


    '---> Step 02,03,04,05
    For K = 1 To 2
        For I = 22 To 23
            If K = 1 And I = 22 Then
                Col = "C"
                fcol = "B"
            ElseIf K = 1 And I = 23 Then
                Col = "C"
                fcol = "D"
            ElseIf K = 2 And I = 22 Then
                Col = "G"
                fcol = "F"
            ElseIf K = 2 And I = 23 Then
                Col = "G"
                fcol = "H"
            End If
                   
            Min = WS.Range("C" & I) - 4
            Max = WS.Range("C" & I) + 4
            For J = 2 To 11
                If WS.Range(Col & J) >= Min And WS.Range(Col & J) <= Max Then
                    factor = WS.Range(Col & J).Offset(0, -2)
                    If factor = "Close Hits" Or factor = "Point Hits" Then factor = "Unity"
                    Exit For
                End If
            Next J
            
            If factor <> "" Then
                Set cCell = WS.Range("A39:A48").Find(what:=factor, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
                If Not cCell Is Nothing Then
                    WS.Cells(cCell.Row, fcol) = WS.Cells(cCell.Row, fcol) + 1
                End If
                factor = ""
            End If
        Next I
    Next K

End If



'---> Low
If WS.Range("C29") = "Low" Then
    WS.Range("B60") = WS.Range("B60") + 1
    WS.Range("D60") = WS.Range("D60") + 1
    WS.Range("F60") = WS.Range("F60") + 1
    WS.Range("H60") = WS.Range("H60") + 1


    '---> Step 02,03,04,05
    For K = 1 To 2
        For I = 22 To 23
            If K = 1 And I = 22 Then
                Col = "C"
                fcol = "B"
            ElseIf K = 1 And I = 23 Then
                Col = "C"
                fcol = "D"
            ElseIf K = 2 And I = 22 Then
                Col = "G"
                fcol = "F"
            ElseIf K = 2 And I = 23 Then
                Col = "G"
                fcol = "H"
            End If
                   
            Min = WS.Range("C" & I) - 4
            Max = WS.Range("C" & I) + 4
            For J = 11 To 20
                If WS.Range(Col & J) >= Min And WS.Range(Col & J) <= Max Then
                    factor = WS.Range(Col & J).Offset(0, -2)
                    If factor = "Close Hits" Or factor = "Point Hits" Then factor = "Unity"
                    Exit For
                End If
            Next J
            
            If factor <> "" Then
                Set cCell = WS.Range("A50:A59").Find(what:=factor, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
                If Not cCell Is Nothing Then
                    WS.Cells(cCell.Row, fcol) = WS.Cells(cCell.Row, fcol) + 1
                End If
                factor = ""
            End If
        Next I
    Next K

End If



WS.Range("B22") = WS.Range("H32")
WS.Range("B23") = WS.Range("I32")
WS.Range("B24") = WS.Range("J33")
WS.Range("B25") = WS.Range("K33")
WS.Range("B26") = WS.Range("L33")
WS.Range("B27") = WS.Range("G33")
WS.Range("B28") = WS.Range("B33")
WS.Range("C29") = WS.Range("A33")


'---> High
If WS.Range("C29") = "High" Then
    WS.Range("L49") = WS.Range("L49") + 1
    WS.Range("N49") = WS.Range("N49") + 1
    WS.Range("P49") = WS.Range("P49") + 1
    WS.Range("R49") = WS.Range("R49") + 1
    WS.Range("T49") = WS.Range("T49") + 1
    WS.Range("V49") = WS.Range("V49") + 1


    '---> Step 02,03,04,05
    For K = 1 To 3
        For I = 22 To 23
            If K = 1 And I = 22 Then
                Col = "K"
                fcol = "L"
            ElseIf K = 1 And I = 23 Then
                Col = "K"
                fcol = "N"
            ElseIf K = 2 And I = 22 Then
                Col = "O"
                fcol = "P"
            ElseIf K = 2 And I = 23 Then
                Col = "O"
                fcol = "R"
            ElseIf K = 3 And I = 22 Then
                Col = "S"
                fcol = "T"
            ElseIf K = 3 And I = 23 Then
                Col = "S"
                fcol = "V"
            End If
                   
            Min = WS.Range("C" & I) - 4
            Max = WS.Range("C" & I) + 4
            For J = 2 To 11
                If WS.Range(Col & J) >= Min And WS.Range(Col & J) <= Max Then
                    factor = WS.Range(Col & J).Offset(0, -2)
                    If factor = "Range CC Hits" Or factor = "Range HL Hits" Or factor = "Range PC Hits" Then factor = "Unity"
                    Exit For
                End If
            Next J
            
            If factor <> "" Then
                Set cCell = WS.Range("K39:K48").Find(what:=factor, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
                If Not cCell Is Nothing Then
                    WS.Cells(cCell.Row, fcol) = WS.Cells(cCell.Row, fcol) + 1
                End If
                factor = ""
            End If
        Next I
    Next K

End If



'---> Low
If WS.Range("C29") = "Low" Then
    WS.Range("L60") = WS.Range("L60") + 1
    WS.Range("N60") = WS.Range("N60") + 1
    WS.Range("P60") = WS.Range("P60") + 1
    WS.Range("R60") = WS.Range("R60") + 1
    WS.Range("T60") = WS.Range("T60") + 1
    WS.Range("V60") = WS.Range("V60") + 1



    '---> Step 02,03,04,05
    For K = 1 To 3
        For I = 22 To 23
            If K = 1 And I = 22 Then
                Col = "K"
                fcol = "L"
            ElseIf K = 1 And I = 23 Then
                Col = "K"
                fcol = "N"
            ElseIf K = 2 And I = 22 Then
                Col = "O"
                fcol = "P"
            ElseIf K = 2 And I = 23 Then
                Col = "O"
                fcol = "R"
            ElseIf K = 3 And I = 22 Then
                Col = "S"
                fcol = "T"
            ElseIf K = 3 And I = 23 Then
                Col = "S"
                fcol = "V"
            End If
                   
            Min = WS.Range("C" & I) - 4
            Max = WS.Range("C" & I) + 4
            For J = 11 To 20
                If WS.Range(Col & J) >= Min And WS.Range(Col & J) <= Max Then
                    factor = WS.Range(Col & J).Offset(0, -2)
                    If factor = "Close Hits" Or factor = "Point Hits" Then factor = "Unity"
                    Exit For
                End If
            Next J
            
            If factor <> "" Then
                Set cCell = WS.Range("K50:K59").Find(what:=factor, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
                If Not cCell Is Nothing Then
                    WS.Cells(cCell.Row, fcol) = WS.Cells(cCell.Row, fcol) + 1
                End If
                factor = ""
            End If
        Next I
    Next K

End If

WS.Range("B22") = WS.Range("H33")
WS.Range("B23") = WS.Range("I33")
WS.Range("B24") = WS.Range("J32")
WS.Range("B25") = WS.Range("K32")
WS.Range("B26") = WS.Range("L32")
WS.Range("B27") = WS.Range("G33")
WS.Range("B28") = WS.Range("B33")
WS.Range("C29") = WS.Range("A33")


'---> High
If WS.Range("C29") = "High" Then
    WS.Range("L76") = WS.Range("L76") + 1
    WS.Range("N76") = WS.Range("N76") + 1
    WS.Range("P76") = WS.Range("P76") + 1
    WS.Range("R76") = WS.Range("R76") + 1
    WS.Range("T76") = WS.Range("T76") + 1
    WS.Range("V76") = WS.Range("V76") + 1



    '---> Step 02,03,04,05
    For K = 1 To 3
        For I = 22 To 23
            If K = 1 And I = 22 Then
                Col = "K"
                fcol = "L"
            ElseIf K = 1 And I = 23 Then
                Col = "K"
                fcol = "N"
            ElseIf K = 2 And I = 22 Then
                Col = "O"
                fcol = "P"
            ElseIf K = 2 And I = 23 Then
                Col = "O"
                fcol = "R"
            ElseIf K = 3 And I = 22 Then
                Col = "S"
                fcol = "T"
            ElseIf K = 3 And I = 23 Then
                Col = "S"
                fcol = "V"
            End If
                   
            Min = WS.Range("C" & I) - 4
            Max = WS.Range("C" & I) + 4
            For J = 2 To 11
                If WS.Range(Col & J) >= Min And WS.Range(Col & J) <= Max Then
                    factor = WS.Range(Col & J).Offset(0, -2)
                    If factor = "Range CC Hits" Or factor = "Range HL Hits" Or factor = "Range PC Hits" Then factor = "Unity"
                    Exit For
                End If
            Next J
            
            If factor <> "" Then
                Set cCell = WS.Range("K66:K75").Find(what:=factor, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
                If Not cCell Is Nothing Then
                    WS.Cells(cCell.Row, fcol) = WS.Cells(cCell.Row, fcol) + 1
                End If
                factor = ""
            End If
        Next I
    Next K

End If



'---> Low
If WS.Range("C29") = "Low" Then
    WS.Range("L87") = WS.Range("L87") + 1
    WS.Range("N87") = WS.Range("N87") + 1
    WS.Range("P87") = WS.Range("P87") + 1
    WS.Range("R87") = WS.Range("R87") + 1
    WS.Range("T87") = WS.Range("T87") + 1
    WS.Range("V87") = WS.Range("V87") + 1



    '---> Step 02,03,04,05
    For K = 1 To 3
        For I = 22 To 23
            If K = 1 And I = 22 Then
                Col = "K"
                fcol = "L"
            ElseIf K = 1 And I = 23 Then
                Col = "K"
                fcol = "N"
            ElseIf K = 2 And I = 22 Then
                Col = "O"
                fcol = "P"
            ElseIf K = 2 And I = 23 Then
                Col = "O"
                fcol = "R"
            ElseIf K = 3 And I = 22 Then
                Col = "S"
                fcol = "T"
            ElseIf K = 3 And I = 23 Then
                Col = "S"
                fcol = "V"
            End If
                   
            Min = WS.Range("C" & I) - 4
            Max = WS.Range("C" & I) + 4
            For J = 11 To 20
                If WS.Range(Col & J) >= Min And WS.Range(Col & J) <= Max Then
                    factor = WS.Range(Col & J).Offset(0, -2)
                    If factor = "Close Hits" Or factor = "Point Hits" Then factor = "Unity"
                    Exit For
                End If
            Next J
            
            If factor <> "" Then
                Set cCell = WS.Range("K77:K86").Find(what:=factor, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
                If Not cCell Is Nothing Then
                    WS.Cells(cCell.Row, fcol) = WS.Cells(cCell.Row, fcol) + 1
                End If
                factor = ""
            End If
        Next I
    Next K

End If

WS.Range("B22") = WS.Range("H33")
WS.Range("B23") = WS.Range("I33")
WS.Range("B24") = WS.Range("J33")
WS.Range("B25") = WS.Range("K33")
WS.Range("B26") = WS.Range("L33")
WS.Range("B27") = WS.Range("G33")
WS.Range("B28") = WS.Range("B33")
WS.Range("C29") = WS.Range("A33")



'---> High
If WS.Range("C29") = "High" Then
    WS.Range("L103") = WS.Range("L103") + 1
    WS.Range("N103") = WS.Range("N103") + 1
    WS.Range("P103") = WS.Range("P103") + 1
    WS.Range("R103") = WS.Range("R103") + 1
    WS.Range("T103") = WS.Range("T103") + 1
    WS.Range("V103") = WS.Range("V103") + 1




    '---> Step 02,03,04,05
    For K = 1 To 3
        For I = 22 To 23
            If K = 1 And I = 22 Then
                Col = "K"
                fcol = "L"
            ElseIf K = 1 And I = 23 Then
                Col = "K"
                fcol = "N"
            ElseIf K = 2 And I = 22 Then
                Col = "O"
                fcol = "P"
            ElseIf K = 2 And I = 23 Then
                Col = "O"
                fcol = "R"
            ElseIf K = 3 And I = 22 Then
                Col = "S"
                fcol = "T"
            ElseIf K = 3 And I = 23 Then
                Col = "S"
                fcol = "V"
            End If
                   
            Min = WS.Range("C" & I) - 4
            Max = WS.Range("C" & I) + 4
            For J = 2 To 11
                If WS.Range(Col & J) >= Min And WS.Range(Col & J) <= Max Then
                    factor = WS.Range(Col & J).Offset(0, -2)
                    If factor = "Range CC Hits" Or factor = "Range HL Hits" Or factor = "Range PC Hits" Then factor = "Unity"
                    Exit For
                End If
            Next J
            
            If factor <> "" Then
                Set cCell = WS.Range("K93:K102").Find(what:=factor, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
                If Not cCell Is Nothing Then
                    WS.Cells(cCell.Row, fcol) = WS.Cells(cCell.Row, fcol) + 1
                End If
                factor = ""
            End If
        Next I
    Next K

End If



'---> Low
If WS.Range("C29") = "Low" Then
    WS.Range("L114") = WS.Range("L114") + 1
    WS.Range("N114") = WS.Range("N114") + 1
    WS.Range("P114") = WS.Range("P114") + 1
    WS.Range("R114") = WS.Range("R114") + 1
    WS.Range("T114") = WS.Range("T114") + 1
    WS.Range("V114") = WS.Range("V114") + 1




    '---> Step 02,03,04,05
    For K = 1 To 3
        For I = 22 To 23
            If K = 1 And I = 22 Then
                Col = "K"
                fcol = "L"
            ElseIf K = 1 And I = 23 Then
                Col = "K"
                fcol = "N"
            ElseIf K = 2 And I = 22 Then
                Col = "O"
                fcol = "P"
            ElseIf K = 2 And I = 23 Then
                Col = "O"
                fcol = "R"
            ElseIf K = 3 And I = 22 Then
                Col = "S"
                fcol = "T"
            ElseIf K = 3 And I = 23 Then
                Col = "S"
                fcol = "V"
            End If
                   
            Min = WS.Range("C" & I) - 4
            Max = WS.Range("C" & I) + 4
            For J = 11 To 20
                If WS.Range(Col & J) >= Min And WS.Range(Col & J) <= Max Then
                    factor = WS.Range(Col & J).Offset(0, -2)
                    If factor = "Close Hits" Or factor = "Point Hits" Then factor = "Unity"
                    Exit For
                End If
            Next J
            
            If factor <> "" Then
                Set cCell = WS.Range("K104:K113").Find(what:=factor, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
                If Not cCell Is Nothing Then
                    WS.Cells(cCell.Row, fcol) = WS.Cells(cCell.Row, fcol) + 1
                End If
                factor = ""
            End If
        Next I
    Next K

End If

WS.Range("B22") = WS.Range("H33")
WS.Range("B23") = WS.Range("I33")
WS.Range("B24") = WS.Range("J33")
WS.Range("B25") = WS.Range("K33")
WS.Range("B26") = WS.Range("L33")
WS.Range("B27") = WS.Range("G32")
WS.Range("B28") = WS.Range("B32")
WS.Range("C29") = WS.Range("A33")



'---> High
If WS.Range("C29") = "High" Then
    WS.Range("B76") = WS.Range("B76") + 1
    WS.Range("D76") = WS.Range("D76") + 1
    WS.Range("F76") = WS.Range("F76") + 1
    WS.Range("H76") = WS.Range("H76") + 1



    '---> Step 02,03,04,05
    For K = 1 To 2
        For I = 22 To 23
            If K = 1 And I = 22 Then
                Col = "C"
                fcol = "B"
            ElseIf K = 1 And I = 23 Then
                Col = "C"
                fcol = "D"
            ElseIf K = 2 And I = 22 Then
                Col = "G"
                fcol = "F"
            ElseIf K = 2 And I = 23 Then
                Col = "G"
                fcol = "H"
            End If
                   
            Min = WS.Range("C" & I) - 4
            Max = WS.Range("C" & I) + 4
            For J = 2 To 11
                If WS.Range(Col & J) >= Min And WS.Range(Col & J) <= Max Then
                    factor = WS.Range(Col & J).Offset(0, -2)
                    If factor = "Close Hits" Or factor = "Point Hits" Then factor = "Unity"
                    Exit For
                End If
            Next J
            
            If factor <> "" Then
                Set cCell = WS.Range("A66:A75").Find(what:=factor, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
                If Not cCell Is Nothing Then
                    WS.Cells(cCell.Row, fcol) = WS.Cells(cCell.Row, fcol) + 1
                End If
                factor = ""
            End If
        Next I
    Next K

End If



'---> Low
If WS.Range("C29") = "Low" Then
    WS.Range("B87") = WS.Range("B87") + 1
    WS.Range("D87") = WS.Range("D87") + 1
    WS.Range("F87") = WS.Range("F87") + 1
    WS.Range("H87") = WS.Range("H87") + 1



    '---> Step 02,03,04,05
    For K = 1 To 2
        For I = 22 To 23
            If K = 1 And I = 22 Then
                Col = "C"
                fcol = "B"
            ElseIf K = 1 And I = 23 Then
                Col = "C"
                fcol = "D"
            ElseIf K = 2 And I = 22 Then
                Col = "G"
                fcol = "F"
            ElseIf K = 2 And I = 23 Then
                Col = "G"
                fcol = "H"
            End If
                   
            Min = WS.Range("C" & I) - 4
            Max = WS.Range("C" & I) + 4
            For J = 11 To 20
                If WS.Range(Col & J) >= Min And WS.Range(Col & J) <= Max Then
                    factor = WS.Range(Col & J).Offset(0, -2)
                    If factor = "Close Hits" Or factor = "Point Hits" Then factor = "Unity"
                    Exit For
                End If
            Next J
            
            If factor <> "" Then
                Set cCell = WS.Range("A77:A86").Find(what:=factor, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
                If Not cCell Is Nothing Then
                    WS.Cells(cCell.Row, fcol) = WS.Cells(cCell.Row, fcol) + 1
                End If
                factor = ""
            End If
        Next I
    Next K

End If

WS.Range("B22:B28").ClearContents
WS.Range("C29").ClearContents


End Sub

Open in new window


There is 5 Ways calculations & each result is registered in 5 tables for Highs & lows.

1. Update Range B22:B28 & C29. Then Update Column B D F H in Table 1 row 39 to 60 for High & Low.
Update Ranges i.e. WS.Range("B22") = WS.Range("H33")
WS.Range("B23") = WS.Range("I33")
WS.Range("B27") = WS.Range("B33")
WS.Range("B28") = WS.Range("G33")
WS.Range("C29") = WS.Range("A33")

2.Same way Update Range B22:B28 & C29. Then Update Column L N P R T V in Table 2 row  39 to 60 for High & low.

3.Update range B22:B28 & C29. Then Update Column L N P R T V in Table 3 row 66 to 87 for High & low.

4.Update Range B22:B28 & C29. Then Update Column L N P R T V in Table 4 Row 93 to  114  for High & Low.

5.Update Range B22:B28 & C29. Then Update Column B D F H in Table 5 row 66 to 87 for High & low.

Thanks
Scoring-G-V07.xlsm
0
Comment
Question by:itjockey
  • 14
  • 12
26 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39856188
Are you sure table 4 and 5 are correct ? they shouldn't be inverted ?

like if you go left right then down then left right then down ???
as you have
1    2
5    3
      4

I would see it
1    2
3    4
5

???
gowlfow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39856212
like if you go left right then down then left right then down ???
as you have
1    2
5    3
      4

yes it is right one as per Sub Analyse. if you want to change order then need to change Sub sequence too.

thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39856213
Also your post does not reflect the code

You have first pass
WS.Range("B22") = WS.Range("H33")
WS.Range("B23") = WS.Range("I33")
WS.Range("B27") = WS.Range("B33")
WS.Range("B28") = WS.Range("G33")
WS.Range("C29") = WS.Range("A33")

Open in new window


Second Pass
WS.Range("B22") = WS.Range("H32")
WS.Range("B23") = WS.Range("I32")
WS.Range("B24") = WS.Range("J33")
WS.Range("B25") = WS.Range("K33")
WS.Range("B26") = WS.Range("L33")
WS.Range("B27") = WS.Range("G33")
WS.Range("B28") = WS.Range("B33")
WS.Range("C29") = WS.Range("A33")

Open in new window


Third Pass
WS.Range("B22") = WS.Range("H33")
WS.Range("B23") = WS.Range("I33")
WS.Range("B24") = WS.Range("J32")
WS.Range("B25") = WS.Range("K32")
WS.Range("B26") = WS.Range("L32")
WS.Range("B27") = WS.Range("G33")
WS.Range("B28") = WS.Range("B33")
WS.Range("C29") = WS.Range("A33")

Open in new window


Fourth PAss
WS.Range("B22") = WS.Range("H33")
WS.Range("B23") = WS.Range("I33")
WS.Range("B24") = WS.Range("J33")
WS.Range("B25") = WS.Range("K33")
WS.Range("B26") = WS.Range("L33")
WS.Range("B27") = WS.Range("G33")
WS.Range("B28") = WS.Range("B33")
WS.Range("C29") = WS.Range("A33")

Open in new window


Fifth Pass
WS.Range("B22") = WS.Range("H33")
WS.Range("B23") = WS.Range("I33")
WS.Range("B24") = WS.Range("J33")
WS.Range("B25") = WS.Range("K33")
WS.Range("B26") = WS.Range("L33")
WS.Range("B27") = WS.Range("G32")
WS.Range("B28") = WS.Range("B32")
WS.Range("C29") = WS.Range("A33")

Open in new window


and nothing of all this is mentioned in your post note the difference sometimes row 32 sometimes 33 only .... what is it ?

gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39856383
Sir give me some time as I am on my way back to home. Traveling.

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 39857066
ok sorry for delay in reply.

A. all  table working with 3 types of data & combination of any two.TIME - PRICE - PRICE RANGES

B.TIME - PRICE - PRICE RANGES -two types - Current & previous
Current is available in row 33. (see date - it is latest then row 32)
previous is available in row 32.

I had labeled in each table top row.
Current Price With Current Time Range - Works with - Current TIME & current PRICES
Previous Time Range With Current Price Range                                                                  
- works with previous TIME & current PRICE RANGES.
rest all 3 as name suggested works with above combinations.

See attached - highlighted cells.

thanks
Scoring-G-V07.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39857302
All this explanation is fine for you but does not serve me. I cannot start learning prices and stocks. But I know how to program vba.

I noted in my last post some inconsistencies in what I saw versus what you asked in oyur question.

YOUR JOB
is to check what I posted thouroully for each step and make sure what I posted for each step is correct and then admit that you made a mistake in oyur initial post and did  not mention these for each pass.

That was the purpose of my post is to make sure that what I have in code is correct.

and then and only then I can start optimizing the code.

gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39858160
Sir gowflow,

Now do u want me to post steps in detail?


Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39858401
it seems you don't get it !!!
I need the important steps that make the distinction between passes. If you feel your 5 pages are what it need then let it be.

Just don't copy paste all the steps here but attach a document that have the explanation.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39858749
i wont available for today.....as leaving office early. Going to Doctor.


hey Expert one question do u know why WBC count increases ?
i don't know why my WBC count doubled then normal range from last two day & still i dint find nothing unusual in body.  :)


 just chilling ....i thought need to inform as i am not available....(Online on Cell)

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39858895

hey Expert one question do u know why WBC count increases ?
i don't know why my WBC count doubled then normal range from last two day & still i dint find nothing unusual in body.  :)

I doubt this is an Excel question !!! I presume it is a medical question so the best qualified is not EE Expert (or they may be some) but rather a DOCTOR !

Hope all is fine with you and you will feel well in no time. Take care, ususally lately they found out that most of our disorders orginates from stress  !!! and as you mentioned lately going from office at 430 AM this is called NO SLEEP ... and for me personally when I reach the repetitive lack of sleep my health takes a beat and get sick.

So remedy, sleep and sleep and sleep and rest and get well.
Reagrds
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39859042
Thank You Sir :)
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39863188
If you want an answer you should advise the steps.
gowlfow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39864179
Sorry Sir for delay ....just need confirmation from your side. how would like to see steps? there is two ways High & low together for each pass
Copy Cell Value H33 Past To B22
Copy Cell Value I33 Past To B23
Copy Cell Value B33 Past To B27
Copy Cell Value G33 Past To B28
Copy Cell Value A33 Past To C29


If C29 = "High" then +1 @ Cell B76 & D76 & F76 & H76
If C29="Low" then +1 @ CellB87 & D87 & F87 & H87

If C29="High" then Find C22 Value From Range C2:C10(+ or - 4 Point Leverage)
"If True then find Respective factor in range B39:B48& Add +1 (if  it is at Range CC then put +1 @ Unity)else nothing next step
"
If C29="Low" then Find C22 Value From Range C12:C20(+ or - 4 Point Leverage)
"If True then find Respective factor in range B49:B57& Add +1 (if  it is at Range CC then put +1 @ Unity)else nothing next step
"


If C29="High" then Find C23 value in range C2:C11(+ or - 4 Point Leverage)
"If True then find Respective factor in range D39:D48& Add +1 (if  it is at Range CC then put +1 @ Unity)else nothing next step
"
If C29="Low" then Find C23 Value From Range C12:C20(+ or - 4 Point Leverage)
"If True then find Respective factor in range D49:D57& Add +1 (if  it is at Range CC then put +1 @ Unity)else nothing next step
"

Open in new window

or separated for High & low for each pass? this is only for High for one of the pass
Copy Cell Value H33 Past To B22
Copy Cell Value I33 Past To B23
Copy Cell Value B33 Past To B27
Copy Cell Value G33 Past To B28
Copy Cell Value A33 Past To C29


If C29 = "High" then +1 @ Cell B76 & D76 & F76 & H76

If C29="High" then Find C22 Value From Range C2:C10(+ or - 4 Point Leverage)
"If True then find Respective factor in range B39:B48& Add +1 (if  it is at Range CC then put +1 @ Unity)else nothing next step
"

If C29="High" then Find C23 value in range C2:C11(+ or - 4 Point Leverage)
"If True then find Respective factor in range D39:D48& Add +1 (if  it is at Range CC then put +1 @ Unity)else nothing next step
"

Open in new window


both are just samples not full steps.

Thanks
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 29

Expert Comment

by:gowflow
ID: 39864193
I want them exactly how the logic should be.

If the logic dictate that for each pass you process something, then high then low well I just need this in very summary.

If the logic says that you should process all the high then process something then update table1 then something then table2 etc... and when finished process low then something then Table1 etc... then be it

If the logic says that you should update A,B,C for Table1 High and then low then D,E,F for table2 high then low .... then be it

I need you to explain (in plain English) meaning not in code not copy cell b1 paste b33 ... but in meaning copy the cell affection then process for column A,B,C high etc....
like this. For sure in the Cell affection is not the same then you can copy paste the code for that step.

gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39864359
ok this is for pass 1  - Scoring Table  1"Current Price With Current Time Range"
where current Prices & current time is in row 33.we required CD TD  Close Hits & Point Hits values form row 33 & put in table A22:C29 to there respective heads.
so in row 33 CD is 37 (Cell H33 ) TD is 25 (Cell I33) Close Hits is 5288.95 (Cell G33) & Point Hits is 5108.25 (Cell B33). i.e.
Copy Cell Value H33 Past To B22
Copy Cell Value I33 Past To B23
Copy Cell Value B33 Past To B27
Copy Cell Value G33 Past To B28
Copy Cell Value A33 Past To C29

Open in new window

this Step 1 for pass 1.

Now we are counting one result so need to add +1 to High if there is high & +1 to low if there is low in Total From High & Total From low respectively in Scoring Table 1.
If C29 = "High" then +1 @ Cell B49 & D49 & F49 & H49
If C29="Low" then +1 @ CellB60 & D60 & F60 & H60

Open in new window

Step 2 Pass 1

Step 3 If C29="High" then Find C22 Value From Range C2:C10(+ or - 4 Point Leverage)
If True then find Respective factor in range B39:B48& Add +1 (if  it is at Close Hits then put +1 @ Unity)else nothing next step

If C29="Low" then Find C22 Value From Range C12:C20(+ or - 4 Point Leverage)
If True then find Respective factor in range B49:B57& Add +1 (if  it is at Close Hits then put +1 @ Unity)else nothing next step



Step 4If C29="High" then Find C23 value in range C2:C11(+ or - 4 Point Leverage)
If True then find Respective factor in range D39:D48& Add +1 (if  it is at Close Hits then put +1 @ Unity)else nothing next step

If C29="Low" then Find C23 Value From Range C12:C20(+ or - 4 Point Leverage)
If True then find Respective factor in range D49:D57& Add +1 (if  it is at Close Hits then put +1 @ Unity)else nothing next step


Step 5  If C29="High" then Find C22 value in range G2:G11(+ or - 4 Point Leverage)
If True then find Respective factor in range F39:F48& Add +1 (if  it is at Point Hits then put +1 @ Unity)else nothing next step

If C29="Low" then Find C22 Value From Range G12:G20(+ or - 4 Point Leverage)
If True then find Respective factor in range F49:F57& Add +1 (if  it is at Point Hits then put +1 @ Unity)else nothing next step


Step 6 If C29="High" then Find C23 value in range G2:G11(+ or - 4 Point Leverage)
If True then find Respective factor in range H39:H48& Add +1 (if  it is at Point Hits then put +1 @ Unity)else nothing next step

If C29="Low" then Find C23 Value From Range G12:G20(+ or - 4 Point Leverage)
If True then find Respective factor in range H49:H57& Add +1 (if  it is at Point Hits then put +1 @ Unity)else nothing

Pass 1 end


Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 39864381
I had written all pass in this notepad but I guess you don't like this way.....


Thanks
All-Pass.txt
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39864417
last text file is fine. Let me digest all this, and maybe at the end .....
will see

gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39864431
take your time Sir .... actually I feel my self happy
last text file is fine. Let me digest all this, and maybe at the end .....
will see

as I am not good in writings. :)  

Thanks
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39864463
ok Given the complexity and non-uniformity, I have divided into Passes and created literally 1 sub for each pass so that if you have a problem in 1 pass you can check the appropriate code without having to go thru all the code.

There is still the main Sub that is called Analyse that will do the grouping and here is the code for that.

Sub Analyse()
Dim WS As Worksheet

Set WS = ThisWorkbook.ActiveSheet

Analyse_Pass1 WS
Analyse_Pass2 WS
Analyse_Pass3 WS
Analyse_Pass4 WS
Analyse_Pass5 WS

WS.Range("B22:B28").ClearContents
WS.Range("C29").ClearContents

End Sub

Open in new window


For the individual subs pass1, pass2 etc... you can see them in the attached file.

Pls try it and let me know if all is ok.
gowflow
Scoring-G-V08.xlsm
0
 
LVL 8

Author Comment

by:itjockey
ID: 39864485
Working perfect Sir......
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39864639
Actually for working it should work same as before it is only the structure of the whole Sub we changed to make it easier to dissect and troubleshoot and also for adding/deleting things then it become much simpler to deal with 1 pass instead of being lost in a sub that is 500 km long !!

btw, I see you are very active in the Excel Zone, Good for you nice to see that.
gowflow
0
 
LVL 8

Author Closing Comment

by:itjockey
ID: 39864754
Thank You Sir for compliment & thank you for this question. As I don't know about this to split subs which benefited in future
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39864765
Your welcome and glad I could help. Any news on the Stat question issue ?
Regards
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39864811
Nope Sir stuck between how to get this done via formula.is that way to call this procedure via VBA?

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 39864843
It just comment :- As more more my question you will solve of mine. You will  become trader one day.    ;-)
My self I am trader at institutional desk.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39864921
I could guess ur a trader I always loved trading but never had the opportunity !!!

as to solving this via VBA well will need at the end the exact formulas or else we are stuck !!!
gowflow
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This collection of functions covers all the normal rounding methods of just about any numeric value.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now