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

Macro Lessons??

Topic closed. 8 replies. Last post 11 years ago by lottaloot.

Page 1 of 1
PrintE-mailLink
powerplayer's avatar - Lottery-022.jpg

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

Hello all,

I know we have had a lot of people ask excel and VB questions.

I am trying to learn macro's to make things easier with a automated task.

I'm a little confused on how to go about doing this.

Here is what I have:

In Cell A1 going down I have the folling:

0987 0986 0985 0984 0983 0982 0981 0976 0975 0974 0973 0972 0971 0965 0964 0963 0962 0961 0954 0953 0952 0951 0943 0942 0941 0932 0931 0921 0876 0875 0874 0873 0872 0871 0865 0864 0863 0862 0861 0854 0853 0852 0851 0843 0842 0841 0832 0831 0821 0765 0764 0763 0762 0761 0754 0753 0752 0751 0743 0742 0741 0732 0731 0721 0654 0653 0652 0651 0643 0642 0641 0632 0631 0621 0543 0542 0541 0532 0531 0521 0432 0431 0421 0321

In cell B1 I have the following going down:

0789
0798
0879
0897
0987
0978
7089
7098
7890
7809
7980
7908
8079
8097
8790
8709
8907
8970
9078
9087
9780
9708
9870
9807

As you can see this is the first number in the list. The list in Cell B going down is all 24 combination that you can get in the pick4.

Now I recorded a macro already and it works but, I need to modify it so it will take each set of 4 #'s in A1,A2,A3 ect. and put it in B1 or next available cell skipping a cell block inbetween.

Example:

A1 has the list. B1 has the first 24 combo's then skip a row of cell and the next row it would start at is D1 to D24.

Well that is it. Below is the macro I wrote I just need some advise on how to change the macro to fit what I'm doing.

 

Sub play424waybox()
'
' play424waybox Macro
' Macro recorded 1/27/2006 by Administrator
'

'
    ActiveCell.FormulaR1C1 = "0369"
    RaPge("P26").Select
    ActiveCell.FormulaR1C1 = "0396"
    RaPge("P27").Select
    ActiveCell.FormulaR1C1 = "0639"
    RaPge("P28").Select
    ActiveCell.FormulaR1C1 = "0693"
    RaPge("P29").Select
    ActiveCell.FormulaR1C1 = "0936"
    RaPge("P30").Select
    ActiveCell.FormulaR1C1 = "0963"
    RaPge("P31").Select
    ActiveCell.FormulaR1C1 = "3069"
    RaPge("P32").Select
    ActiveCell.FormulaR1C1 = "3096"
    RaPge("P33").Select
    ActiveCell.FormulaR1C1 = "3609"
    RaPge("P33").Select
    ActiveCell.FormulaR1C1 = "3690"
    RaPge("P35").Select
    ActiveCell.FormulaR1C1 = "3906"
    RaPge("P36").Select
    ActiveCell.FormulaR1C1 = "3960"
    RaPge("P37").Select
    ActiveCell.FormulaR1C1 = "6039"
    RaPge("P38").Select
    ActiveCell.FormulaR1C1 = "6093"
    RaPge("P39").Select
    ActiveCell.FormulaR1C1 = "6309"
    RaPge("P40").Select
    ActiveCell.FormulaR1C1 = "6390"
    RaPge("P41").Select
    ActiveCell.FormulaR1C1 = "6903"
    RaPge("P42").Select
    ActiveCell.FormulaR1C1 = "6930"
    RaPge("P43").Select
    ActiveCell.FormulaR1C1 = "9036"
    RaPge("P44").Select
    ActiveCell.FormulaR1C1 = "9063"
    RaPge("P45").Select
    ActiveCell.FormulaR1C1 = "9306"
    RaPge("P46").Select
    ActiveCell.FormulaR1C1 = "9360"
    RaPge("P47").Select
    ActiveCell.FormulaR1C1 = "9603"
    RaPge("P48").Select
    ActiveCell.FormulaR1C1 = "9630"
    RaPge("P49").Select
EPd Sub

There is 10,000 numbers so a macro would be a great thing to use.

Any help would be great!!!

Thank you

PP

Good luck to everyone!!!

    paurths's avatar - underground
    Switching between Fairfax, VA and Belgium
    Belgium
    Member #19287
    July 29, 2005
    2254 Posts
    Offline
    Posted: January 28, 2006, 7:26 am - IP Logged

    I don't think there are 10000 columns available, Powerplayer...
    It will loop until it finds an empty cell in column A.
    (i have this in a proceducre somewhere where an array is used to create the 24-ways, but i can't seem to locate it lol) 

    Private sub sbMake24Way() 

    Dim iRows As Integer
    Dim iWCol As Integer
    Dim s1 As String
    Dim s2 As String
    Dim s3 As String
    Dim s4 As String
    Dim blnEnd As Boolean

        iWCol = 2
        iRows = 1

        Do Until blnEnd
            'Dummy variable, blnEnd (not needed in this setup ofcourse... just for the loop)
            If trim(Cells(iRows, 1)) = "" Then Exit Do
           
            s1 = Mid(Cells(iRows, 1), 1, 1)
            s2 = Mid(Cells(iRows, 1), 2, 1)
            s3 = Mid(Cells(iRows, 1), 3, 1)
            s4 = Mid(Cells(iRows, 1), 4, 1)
           
            Cells(1, iWCol) = s1 & s2 & s3 & s4
            Cells(2, iWCol) = s1 & s2 & s4 & s3
            Cells(3, iWCol) = s1 & s3 & s2 & s4
            Cells(4, iWCol) = s1 & s3 & s4 & s2
            Cells(5, iWCol) = s1 & s4 & s2 & s3
            Cells(6, iWCol) = s1 & s4 & s3 & s2

            Cells(7, iWCol) = s2 & s1 & s3 & s4
            Cells(8, iWCol) = s2 & s1 & s4 & s3
            Cells(9, iWCol) = s2 & s3 & s1 & s4
            Cells(10, iWCol) = s2 & s3 & s4 & s1
            Cells(11, iWCol) = s2 & s4 & s1 & s3
            Cells(12, iWCol) = s2 & s4 & s3 & s1
           
            Cells(13, iWCol) = s3 & s1 & s2 & s4
            Cells(14, iWCol) = s3 & s1 & s4 & s2
            Cells(15, iWCol) = s3 & s2 & s1 & s4
            Cells(16, iWCol) = s3 & s2 & s4 & s1
            Cells(17, iWCol) = s3 & s4 & s1 & s2
            Cells(18, iWCol) = s3 & s4 & s2 & s1
           
            Cells(19, iWCol) = s4 & s1 & s2 & s3
            Cells(20, iWCol) = s4 & s1 & s3 & s2
            Cells(21, iWCol) = s4 & s2 & s1 & s3
            Cells(22, iWCol) = s4 & s2 & s3 & s1
            Cells(23, iWCol) = s4 & s3 & s1 & s2
            Cells(24, iWCol) = s4 & s3 & s2 & s1
           
            iWCol = iWCol + 1
            iRows = iRows + 1
        Loop

    End Sub

      paurths's avatar - underground
      Switching between Fairfax, VA and Belgium
      Belgium
      Member #19287
      July 29, 2005
      2254 Posts
      Offline
      Posted: January 28, 2006, 7:34 am - IP Logged

      Oops, just reread your post.

      You want it to skip a column each time?

      Then change this line: iWCol = iWCol + 1

      to : iWCol = iWCol + 2

      Then you'll need 20 000 columns...

        powerplayer's avatar - Lottery-022.jpg

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

        Oops, just reread your post.

        You want it to skip a column each time?

        Then change this line: iWCol = iWCol + 1

        to : iWCol = iWCol + 2

        Then you'll need 20 000 columns...

        WOW thank you Paurths..this is great!!

        Something to do at work tonight...Hurray!Cheers

        Banana

        Banana

        Banana 

        Banana

        Banana

        Banana

        Banana

          powerplayer's avatar - Lottery-022.jpg

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

          Paurths,

          I got another question if you can answer. can you explain what the different code means so I can undstand this more.

          Trying to learn...

          Like what does iw and things like this....

          Dim s1 As String
          Dim s2 As String
          Dim s3 As String
          Dim s4 As String

          the s1 what is this line doing..sorry for the question but, if I understand the code then I will be able to write it.

          Thanks

          PP

            Todd's avatar - Cylon 2.gif
            Chief Bottle Washer
            New Jersey
            United States
            Member #1
            May 31, 2000
            23274 Posts
            Offline
            Posted: January 28, 2006, 9:46 am - IP Logged

            I wouldn't do this kind of thing with a macro.  It's much easier (and more re-usable) to do it using formulas.  If you use formulas, then you can quickly apply the formulas to a large range of cells using copy and paste.

             Many people don't realize how powerful Excel formulas can be.  You can use any VBA functions in your formula, including some pretty powerful string manipulation functions.

            Why not create cells b1, c1, d1, and e1 as each of the four digits?  For example, cell b1 would contain the formula =mid($a1,1,1), cell c1 would contain =mid($a1,2,1), and so on for d1 and e1.

            Then, you can create formulas in cells to the right containing allo of the different combinations:  for example, f1 could contain =b1&c1&d1&e1.

            After creating your 24 columns, you simply highlight cells b1 to the last cell on the right, and "grab" the small square "handle" on the lower-right of the selection and drag it down to copy the formulas.

            Macros can be a good tool, but IMHO should only be used when formulas are not feasible, because macros are harder to maintain, and formulas are much easier to use the next time (you simply plug in the new numbers, and never need to "run" anything).  It's also easier to share spreadsheets that use formulas.

            BTW, that handle-dragging tip for copying the formulas is a great tool to use for a variety of copying tasks.

            For example, if you want to fill a range with numbers, just type the first number, then grab and drag that handle down, holding down either no keys, or the shift, Ctrl, or Alt keys to change what happens when it copies.

             

            Check the State Lottery Report Card
            What grade did your lottery earn?

             

            Sign the Petition for True Lottery Drawings
            Help eliminate computerized drawings!

              powerplayer's avatar - Lottery-022.jpg

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

              Awesome TODD!!! The drag trick I learn from you and use it all the time. Thank you

              I will taket the advise of useing a formula instead. This was actually only to make the chart. So the macro would have only been used and save if needed again. Once it's done creating this chart of all the combinations I'm going to use my other formula that I have with the pick 3 and apply it to this....

              Thank you again........work will be fun tonight..Dance

              PP

                winsumloosesum's avatar - Lottery-060.jpg
                Pennsylvania
                United States
                Member #2218
                September 1, 2003
                5387 Posts
                Offline
                Posted: January 30, 2006, 4:20 pm - IP Logged

                Powerplayer,

                Still need the Pick 4 listing??

                wsls

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

                  Paurths, I love your code. Big Smile Thanks for posting it.  Excel is a pwerful thing.   

                  Powerplayer, good luck!! See Ya!

                  L ttaL   T