Welcome Guest
Log In | Register )
You last visited December 3, 2016, 4:44 pm
All times shown are
Eastern Time (GMT-5:00)

Macros in excel

Topic closed. 62 replies. Last post 11 years ago by time*treat.

Page 4 of 5
PrintE-mailLink
retxx's avatar - mrthumbs
BOSTON
United States
Member #48
September 9, 2001
3584 Posts
Offline
Posted: December 19, 2005, 10:26 pm - IP Logged

lottaloot I am sending you the excel program we talked about to see if you recall anywhere on it that allows for possible plays,and could you answer my question as to moving the entire row down so it gives me the top row blank to add current draw. thanks

    retxx's avatar - mrthumbs
    BOSTON
    United States
    Member #48
    September 9, 2001
    3584 Posts
    Offline
    Posted: December 19, 2005, 10:28 pm - IP Logged

    lottaloot I am sending you the excel program we talked about to see if you recall anywhere on it that allows for possible plays,and could you answer my question as to moving the entire row down so it gives me the top row blank to add current draw. thanks

      lottaloot's avatar - AvatarZ56
      Redford/MI
      United States
      Member #3396
      January 18, 2004
      4867 Posts
      Offline
      Posted: December 19, 2005, 10:44 pm - IP Logged

      lottaloot I am sending you the excel program we talked about to see if you recall anywhere on it that allows for possible plays,and could you answer my question as to moving the entire row down so it gives me the top row blank to add current draw. thanks

      Retxx, I did not create the spreadsheet. 

      You asked if anyone had a sheet that you could put your pick 4 in.  I sent you one. 

      I explained a few posts back how I was informed to add rows for draw results to a sheet that was graciously given to me.

      All I want to do right now is pack my bags, drink my beer, and chill right here in this spot surfing the net. 

      Please allow me to do that. 

      You will have to create you a back-up copy & fiddle around with it like I do. 

       

       

       

      L ttaL   T

        powerplayer's avatar - Lottery-022.jpg

        United States
        Member #17834
        June 28, 2005
        2083 Posts
        Offline
        Posted: December 22, 2005, 5:02 am - IP Logged
         New VBA of the day. If anyone can find a reason for adding this into their spreadsheet,
        please let me know how it can be used. 
         Sub EnterDates()
        Columns(3).Clear
        Dim startDate As String, stopDate As String, startCel As Integer, stopCel As Integer, dateRange As Range
        On Error Resume Next
        Do
            startDate = InputBox("Please enter Start Date:  Format(mm/dd/yy)", "START DATE")
            If startDate = "" Then End
        Loop Until startDate = Format(startDate, "mm/dd/yy") _
            Or startDate = Format(startDate, "m/d/yy")
        Do
            stopDate = InputBox("Please enter Stop Date:  Format(mm/dd/yy)", "STOP DATE")
            If stopDate = "" Then End
        Loop Until stopDate = Format(stopDate, "mm/dd/yy") _
            Or stopDate = Format(stopDate, "m/d/yy")
        startDate = Format(startDate, "mm/dd/yy")
        stopDate = Format(stopDate, "mm/dd/yy")
        startCel = Sheets(1).Columns(1).Find(startDate, LookIn:=xlValues, lookat:=xlWhole).Row
        stopCel = Sheets(1).Columns(1).Find(stopDate, LookIn:=xlValues, lookat:=xlWhole).Row
        On Error GoTo errorHandler
        Set dateRange = Range(Cells(startCel, 1), Cells(stopCel, 1))
        Call CopyWeekDates(dateRange)  ' Passes the argument dateRange to the CopyWeekDates sub.
        Exit Sub
        errorHandler:
            If startCel = 0 Then MsgBox "Start Date is not in table.", 64
            If stopCel = 0 Then MsgBox "Stop Date is not in table.", 64
        End Sub
         
        Sub CopyWeekDates(myRange)
        Dim myDay As Variant, cnt As Integer
        cnt = 1
        For Each myDay In myRange
            If Weekday(myDay, vbMonday) < 6 Then
                With Range("C1")(cnt)
                    .NumberFormat = "mm/dd/yy"
                    .Value = myDay
                End With
            cnt = cnt + 1
            End If
        Next
        End Sub

        lottaloot,

        WTG....You have blown my mind away with all this new Excel and VB stuff.

        I'm still trying to catch up on my lesson I was doing last week.

        WOW this stuff just keeps on going...there is no end.....

        I'm not sure what the coding does?? Can u give a brief description of what this does or should do??
        Powerplayer

        Good luck to everyone!!!

          hypersoniq's avatar - 8ball
          Pennsylvania
          United States
          Member #1340
          April 6, 2003
          2450 Posts
          Offline
          Posted: December 22, 2005, 6:01 am - IP Logged

          here's a challenge I am faced with, a pick3 sheet has a list of the digits across 10 columns (0-9), underneath it has a count of how many times the numbers in each of those columns followed the previous 2 draws. I need to do a lookup in the row for each draw to return the digit most frequent, but LOOKUP() returns bad data because the rows underneath are not sorted.

          I will most likely need to go to vba to solve this one, I will post the code after I figure out the logic.

          Playing more than one ticket per game is betting against yourself.

            hypersoniq's avatar - 8ball
            Pennsylvania
            United States
            Member #1340
            April 6, 2003
            2450 Posts
            Offline
            Posted: December 24, 2005, 1:47 pm - IP Logged

            sometimes I forget things I knew in excel, but didn't use in ages...

            the unsorted lookup works when used differently, I didn't need VBA at all yet for this new sheet

            here is the formula

            =INDEX(Q$1:Z$1,MATCH(AA3,Q3:Z3,0))

            Q1:Z1 =digits 0 thru 9

            Q3:Z3 equals the number of times a digit (in Q1:Z1) followed a particular precursor/draw "combo" at that point in history

            AA3 = LARGE(Q3:Z3,1)

            so, the index formula finds the largets number of announcer occurrences AT THAT POINT from each of the 10 digits and returns the digit for that position for the next draw's pick.

            there is a formula for each position.

            the "rolling history" is accomplished by

            =SUMPRODUCT(--($M$3:$M9879=$M9879),--($J$3:$J9879=Q$1))

            I am still working it out, but I should have reduced the counts by 1 to take away first 1,000 draw "hindsight"

            After calculating thousands of draws (all 9878 draws) I copied the big group of data (all but the last draw), clicked COPY and paste special (under EDIT) to replace the formulas with just their values, cut sheet size by several MB and made recalcs a snap!

            If I need to change the main formulas, I can autofill UP because of the way I anchored certain cell references.

             

             

            Playing more than one ticket per game is betting against yourself.

              retxx's avatar - mrthumbs
              BOSTON
              United States
              Member #48
              September 9, 2001
              3584 Posts
              Offline
              Posted: December 24, 2005, 9:58 pm - IP Logged

              hyper can i send you my pick 4 list of past draws for Mass. to see what you can do with it in an excel spreadsheet. It would be interesting to watch you progress along .thanks
              p s it goes back to 1975.

                hypersoniq's avatar - 8ball
                Pennsylvania
                United States
                Member #1340
                April 6, 2003
                2450 Posts
                Offline
                Posted: December 26, 2005, 2:01 pm - IP Logged

                you already sent me that sheet, I sent it back to you adding Even/Odd,v-trac and High/Low.

                I am developing a straight shooter single combo system for PA pick 3 and pick 4. Pick 3 backtesting is currently PASSING on all but the cyclic hit rate, once that is solved I will be moving it to PA big4, then I will apply it to that MASS sheet you sent. It will analyze ALL draws and give you 1 and only 1 number to play for the next night. All you will have to do is add draws as they happen to the bottom of the list and autofill down (formulas), couldn'tbe easier...

                but it is still in development (follow my blog for progress)

                PA pick 3 is showing a 28 year PROFIT of $26,122 after deducting the cost of play ($9,881). BUT there is a logic error I need to weed out. It counts the most frequent occurrenceof an event but it unfortunately counts the next draw, info you would not have ahead of time....

                Playing more than one ticket per game is betting against yourself.

                  hypersoniq's avatar - 8ball
                  Pennsylvania
                  United States
                  Member #1340
                  April 6, 2003
                  2450 Posts
                  Offline
                  Posted: December 26, 2005, 2:02 pm - IP Logged

                  you already sent me that sheet, I sent it back to you adding Even/Odd,v-trac and High/Low.

                  I am developing a straight shooter single combo system for PA pick 3 and pick 4. Pick 3 backtesting is currently PASSING on all but the cyclic hit rate, once that is solved I will be moving it to PA big4, then I will apply it to that MASS sheet you sent. It will analyze ALL draws and give you 1 and only 1 number to play for the next night. All you will have to do is add draws as they happen to the bottom of the list and autofill down (formulas), couldn'tbe easier...

                  but it is still in development (follow my blog for progress)

                  PA pick 3 is showing a 28 year PROFIT of $26,122 after deducting the cost of play ($9,881). BUT there is a logic error I need to weed out. It counts the most frequent occurrenceof an event but it unfortunately counts the next draw, info you would not have ahead of time....

                  Playing more than one ticket per game is betting against yourself.

                    retxx's avatar - mrthumbs
                    BOSTON
                    United States
                    Member #48
                    September 9, 2001
                    3584 Posts
                    Offline
                    Posted: December 26, 2005, 8:32 pm - IP Logged

                    hyper I am waiting with baited breath. Please give priority to it and inform me while I check your blog for progress. thanks again.

                      hypersoniq's avatar - 8ball
                      Pennsylvania
                      United States
                      Member #1340
                      April 6, 2003
                      2450 Posts
                      Offline
                      Posted: December 26, 2005, 8:36 pm - IP Logged

                      you'll see in the blog that the PA pick4 is done backtesting... 11 straight hits in 25 years

                      might not sound like alot, but at 5,000 a pop, it beats the system by over 40 grand!

                      your MASS data is next in the cue, because I need to test the system on other data anyway, why not make it Mass.? I tend to get a feeling from the nature of some posts that Mass is one tough nut to crack.

                      Playing more than one ticket per game is betting against yourself.

                        lottaloot's avatar - AvatarZ56
                        Redford/MI
                        United States
                        Member #3396
                        January 18, 2004
                        4867 Posts
                        Offline
                        Posted: December 27, 2005, 3:30 pm - IP Logged

                        sometimes I forget things I knew in excel, but didn't use in ages...

                        the unsorted lookup works when used differently, I didn't need VBA at all yet for this new sheet

                        here is the formula

                        =INDEX(Q$1:Z$1,MATCH(AA3,Q3:Z3,0))

                        Q1:Z1 =digits 0 thru 9

                        Q3:Z3 equals the number of times a digit (in Q1:Z1) followed a particular precursor/draw "combo" at that point in history

                        AA3 = LARGE(Q3:Z3,1)

                        so, the index formula finds the largets number of announcer occurrences AT THAT POINT from each of the 10 digits and returns the digit for that position for the next draw's pick.

                        there is a formula for each position.

                        the "rolling history" is accomplished by

                        =SUMPRODUCT(--($M$3:$M9879=$M9879),--($J$3:$J9879=Q$1))

                        I am still working it out, but I should have reduced the counts by 1 to take away first 1,000 draw "hindsight"

                        After calculating thousands of draws (all 9878 draws) I copied the big group of data (all but the last draw), clicked COPY and paste special (under EDIT) to replace the formulas with just their values, cut sheet size by several MB and made recalcs a snap!

                        If I need to change the main formulas, I can autofill UP because of the way I anchored certain cell references.

                         

                         

                        Just catching up on what I missed while I was away. 

                        Pretty neat formulas, Hyper.  Thumbs Up

                         

                        L ttaL   T

                          lottaloot's avatar - AvatarZ56
                          Redford/MI
                          United States
                          Member #3396
                          January 18, 2004
                          4867 Posts
                          Offline
                          Posted: January 4, 2006, 10:17 pm - IP Logged

                          I am getting better.  Big Smile

                          This will allow you to insert a row (or rows depending upon what you change the digit in red to) into your spreadsheet.  Works well for me as I have to add 20 rows for midday & 36 for the evening.

                           

                           

                          Public Sub macro1()
                          Dim lngRow          As Long
                          Dim intCount        As Integer
                           
                          lngRow = 1
                          Do While Cells(lngRow, 1).Value <> ""
                              intCount = 0
                              Do While intCount < 1
                                  Rows(lngRow + 1 & ":" & lngRow + 1).Insert
                                  intCount = intCount + 1
                              Loop
                              lngRow = lngRow + 4
                          Loop
                          End Sub

                          L ttaL   T

                            powerplayer's avatar - Lottery-022.jpg

                            United States
                            Member #17834
                            June 28, 2005
                            2083 Posts
                            Offline
                            Posted: January 12, 2006, 7:11 am - IP Logged

                            sometimes I forget things I knew in excel, but didn't use in ages...

                            the unsorted lookup works when used differently, I didn't need VBA at all yet for this new sheet

                            here is the formula

                            =INDEX(Q$1:Z$1,MATCH(AA3,Q3:Z3,0))

                            Q1:Z1 =digits 0 thru 9

                            Q3:Z3 equals the number of times a digit (in Q1:Z1) followed a particular precursor/draw "combo" at that point in history

                            AA3 = LARGE(Q3:Z3,1)

                            so, the index formula finds the largets number of announcer occurrences AT THAT POINT from each of the 10 digits and returns the digit for that position for the next draw's pick.

                            there is a formula for each position.

                            the "rolling history" is accomplished by

                            =SUMPRODUCT(--($M$3:$M9879=$M9879),--($J$3:$J9879=Q$1))

                            I am still working it out, but I should have reduced the counts by 1 to take away first 1,000 draw "hindsight"

                            After calculating thousands of draws (all 9878 draws) I copied the big group of data (all but the last draw), clicked COPY and paste special (under EDIT) to replace the formulas with just their values, cut sheet size by several MB and made recalcs a snap!

                            If I need to change the main formulas, I can autofill UP because of the way I anchored certain cell references.

                             

                             

                            Hi Hyper,

                            I have a question about this formula.

                            After I put the formula in the cell it doesn't give me a answer...it just says there in the cell (

                            =INDEX(Q$1:Z$1,MATCH(AA3,Q3:Z3,0)

                            I know you have to hit ctrl + shift + enter after putting in the formula but, it won't do anything.

                            I have been having this problem for the last week or so on my spreedsheet.

                            Any help advice from anyone would be great!!

                            Thanks in advance

                            PP

                            Good luck to everyone!!!

                              time*treat's avatar - radar

                              United States
                              Member #13130
                              March 30, 2005
                              2171 Posts
                              Offline
                              Posted: January 25, 2006, 2:00 am - IP Logged

                              Is anyone thinking about gathering all this code we've been creating and putting it all in one spot? Seems we'll have enough for a book shortly. Be useful to have it organized. Not much of an html person myself.

                              In neo-conned Amerika, bank robs you.
                              Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.