Welcome Guest
Log In | Register )
You last visited December 9, 2016, 6:13 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 1 of 5
PrintE-mailLink
lottaloot's avatar - AvatarZ56
Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
Posted: December 15, 2005, 11:30 am - IP Logged

I am very, very frustrated about this macro business within excel. 

I DO NOT understand how to get into the macro section without having to run an imaginary macro-then go in & edit it.  And even then, I don't know if I am doing it right. 

I have ordered books that have not arrived yet to teach myself how to make macros.

 

But isn't there an easier way to talk to excel???

Why can't I just sort of describe what I want it to do & it figure it out for me????Crying 

 

 

L ttaL   T

    powerplayer's avatar - Lottery-022.jpg

    United States
    Member #17834
    June 28, 2005
    2083 Posts
    Offline
    Posted: December 15, 2005, 11:40 am - IP Logged

    I Agree!  Lottaloot there is other ways. Been doing a lot of research on this. Just not sure how yet.

     

    Powerplayer

    Good luck to everyone!!!

      lottaloot's avatar - AvatarZ56
      Redford/MI
      United States
      Member #3396
      January 18, 2004
      4867 Posts
      Offline
      Posted: December 15, 2005, 11:43 am - IP Logged

      Please pass the info along when & if you find it. 

      L ttaL   T

        winsumloosesum's avatar - Lottery-060.jpg
        Pennsylvania
        United States
        Member #2218
        September 1, 2003
        5387 Posts
        Offline
        Posted: December 15, 2005, 12:03 pm - IP Logged

        Kim,

        Don't go pulling all your hair out.

        What is it your trying to do?

        Maybe we can all put our collective thinking caps on and help each other.

        Talk to us girl!!

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

          I want it to do everything!  ROFL 

           

          Seriously, I just want to be able to talk to it.  Right at the moment

          1.  I would just like to search for box skips (without having the combos set up ALL OVER the sheet like I currently have now)

          2.  I would like to have the box skips give me a return of the actual day - Today's date so that I can have a number of skips (i.e, 37 days since 123 last hit on 12/1/05) 

           

          Am I expecting too much????

           

          L ttaL   T

            time*treat's avatar - radar

            United States
            Member #13130
            March 30, 2005
            2171 Posts
            Offline
            Posted: December 16, 2005, 4:50 am - IP Logged
            A little fun in XL97: make sure you have R1C1 box unchecked e.g. letters across, numbers down
             
            Excel - Excel 97 Flight to Credits 
            1. On a new Worksheet, Press F5
            2. Type X97:L97 and hit enter
            3. Press the tab key
            4. Hold Ctrl-Shift
            5. Click on the Chart Wizard button
            6. Use mouse to fly around - Right button forward/ Left button reverse
             
             

              time*treat's avatar - radar

              United States
              Member #13130
              March 30, 2005
              2171 Posts
              Offline
              Posted: December 16, 2005, 5:07 am - IP Logged

              Now... back to work

              tools --> macro --> visual basic editor --> insert --> module

                   

              the best results come from having your data properly organized in the spreadsheet.
              If you can decide how you want your data handled, yes, it can be done.
              Too many people think the statistics tools will magically predict their states numbers for tomorrow.

              I posted one solution to that 'skip count' thing on the other thread http://www.lotterypost.com/thread/124458

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

                powerplayer's avatar - Lottery-022.jpg

                United States
                Member #17834
                June 28, 2005
                2083 Posts
                Offline
                Posted: December 16, 2005, 6:41 am - IP Logged

                I want to automate my spreedsheet a little more. I usually enter all the dates with find and replace useing last months but, want to know what formula would I use to do this:

                02/01/0502/02/0502/03/0502/04/0502/05/0502/06/0502/07/0502/08/0502/09/0502/10/0502/11/0502/12/0502/13/0502/14/0502/15/0502/16/0502/17/0502/18/0502/19/0502/20/0502/21/0502/22/0502/23/0502/24/0502/25/0502/26/0502/27/0502/28/05


                 

                That is Febuary. I want to have a formula that will start with 03/01/05 and then increase across each cell.

                I know there is a bunch of time,year,date formulas but, after trying for a while I was unable to have the formula increase the days.

                I added the analysis tool pak that has more formulas for dates.

                Any help would be great!!

                Powerplayer

                Good luck to everyone!!!

                  time*treat's avatar - radar

                  United States
                  Member #13130
                  March 30, 2005
                  2171 Posts
                  Offline
                  Posted: December 16, 2005, 8:40 am - IP Logged

                   You guys are yanking my chain, right? Ponder

                   '-----------------------------------'

                  Sub calendar()
                      Dim start__cell As Date, next__day As Date, col As Integer
                     
                      start__cell = Cells(1, 1).Value '<--- date goes here'
                      next__day = start__cell + 1
                      col = 2
                     
                      While Month(start__cell) = Month(next__day)
                        Cells(1, col).Value = next__day
                        Cells(1, col).NumberFormat = "mm/dd/yy"
                        col = col + 1
                        next__day = next__day + 1
                      Wend
                  End sub

                   '-----------------------------------'

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

                    Avatar
                    New Mexico
                    United States
                    Member #12305
                    March 10, 2005
                    2984 Posts
                    Offline
                    Posted: December 16, 2005, 9:12 am - IP Logged

                    I am very, very frustrated about this macro business within excel. 

                    I DO NOT understand how to get into the macro section without having to run an imaginary macro-then go in & edit it.  And even then, I don't know if I am doing it right. 

                    I have ordered books that have not arrived yet to teach myself how to make macros.

                     

                    But isn't there an easier way to talk to excel???

                    Why can't I just sort of describe what I want it to do & it figure it out for me????Crying 

                     

                     

                    I posted something once asking about excel and hypersoniq provided this link.  Maybe it will help you.

                    http://www.contextures.com/xladvfilter01.html

                    I learned some stuff that speeded things up for me a bit, but I hit a wall fairly early.  I'd like to use macros but I don't understand what they are.  Not entirely sure, even where filters are concerned.

                    Seems to me there ought to be an easier way of doing all this, so I hope you figure it out.

                    Jack

                    Absorb the good, ignore the bad, weigh the ugly.

                    It's about number behavior.

                    Egos don't count.

                     

                    Dedicated to the memory of Big Loooser

                     

                      powerplayer's avatar - Lottery-022.jpg

                      United States
                      Member #17834
                      June 28, 2005
                      2083 Posts
                      Offline
                      Posted: December 16, 2005, 9:32 am - IP Logged

                      Thank you Time*Treat & Ripsnorter

                      What would I do with out all the LP people in this world.

                      You guys are great..this is excatly what I needed.

                      Thank you again


                      Powerplayer

                      Good luck to everyone!!!

                        time*treat's avatar - radar

                        United States
                        Member #13130
                        March 30, 2005
                        2171 Posts
                        Offline
                        Posted: December 16, 2005, 10:25 am - IP Logged

                        I would say a macro is one way of telling the application what to do with your data. It is a programming language. It is nothing you couldn't do yourself, but it frees you to do other things, or take a nap Sleep while it runs. Of course you have to be detailed. A macro does for your data what a washing machine does for your clothes, but you have to know if you want warm wash / cold rinse and heavy blanket or gentle cycle. Some of the built-ins like histogram and descriptive stats are just macros. If you look at what I posted under powerplayer's request, you can almost match up line-by-line his description with my coding.

                         

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

                          lottaloot's avatar - AvatarZ56
                          Redford/MI
                          United States
                          Member #3396
                          January 18, 2004
                          4867 Posts
                          Offline
                          Posted: December 16, 2005, 11:12 am - IP Logged

                          I am getting there Time*Treat  Wink

                          -------------

                          Sub NoLoop()
                              Dim SearchString
                             
                              SearchString = 123
                              Cells.Find(What:=SearchString, After:=[A1], LookIn:=xlFormulas, _
                              LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                              MatchCase:=False).Activate
                          End Sub

                          -------------

                          Thanks for posting--now it's time to play with yours Big Smile

                          Function lowform_3(three_digit)
                                Dim channel() As Integer
                                Dim slot() As Integer
                                Dim num As Integer, pos As Integer
                                Dim c0, c1, c2, c3
                               
                                c0 = three_digit
                                c1 = Int(c0 / 100)
                                c2 = Int((c0 Mod 100) / 10)
                                c3 = c0 Mod 10
                                ReDim channel(9)
                                ReDim slot(3)
                                channel(c1) = channel(c1) + 1
                                channel(c2) = channel(c2) + 1
                                channel(c3) = channel(c3) + 1
                                pos = 1
                                For num = 0 To 9
                                    While channel(num) > 0
                                      slot(pos) = num
                                      channel(num) = channel(num) - 1
                                      pos = pos + 1
                                    Wend
                                Next num
                                lowform_3 = slot(1) * 100 + slot(2) * 10  + slot(3)
                              End Function

                          L ttaL   T

                            Avatar
                            New Mexico
                            United States
                            Member #12305
                            March 10, 2005
                            2984 Posts
                            Offline
                            Posted: December 16, 2005, 11:41 am - IP Logged

                            I would say a macro is one way of telling the application what to do with your data. It is a programming language. It is nothing you couldn't do yourself, but it frees you to do other things, or take a nap Sleep while it runs. Of course you have to be detailed. A macro does for your data what a washing machine does for your clothes, but you have to know if you want warm wash / cold rinse and heavy blanket or gentle cycle. Some of the built-ins like histogram and descriptive stats are just macros. If you look at what I posted under powerplayer's request, you can almost match up line-by-line his description with my coding.

                             

                            Thanks for the explanation.  I gather a macro would allow me to take a group of numbers similar to this one:

                            3 6 7 18 19 22 23 25 27 39 40 41 47 50
                            19 20 23 25 26 28 29 34 35 39 44 45 47 63
                            1 10 13 16 18 26 27 30 39 44 47 51 55 64
                            2 7 13 24 28 30 38 39 40 44 46 49 53 55
                            5 7 8 21 31 38 41 46 50 52 59 60 67 69
                            1 6 8 14 15 20 29 32 37 38 40 48 52 56
                            1 13 17 18 24 33 35 36 43 46 48 50 54 55
                            1 3 4 9 10 24 26 30 33 36 42 44 45 49
                            5 6 7 15 23 29 36 39 40 42 46 51 52 57
                            2 6 15 18 20 21 26 28 29 36 39 44 57 60
                            1 5 7 9 16 17 23 35 36 37 43 45 50 52
                            1 4 14 16 17 18 23 28 29 33 34 44 52 54
                            12 13 16 21 27 28 34 36 51 55 56 59 60 61
                            4 5 25 30 32 33 34 38 40 41 46 56 58 64
                            5 6 10 12 15 16 19 21 23 26 27 40 49 61
                            3 12 13 16 23 32 35 39 41 42 44 50 51 54
                            3 10 12 18 23 26 27 28 35 38 44 47 49 52
                            6 10 15 17 18 23 25 28 35 41 44 47 48 54
                            16 18 24 28 29 31 34 41 43 53 55 57 59 60
                            1 6 7 9 12 14 16 20 21 25 37 38 41 43

                            And line them up in rows or columns sequencially so I could tell how many there are of each one?  In a couple or three keystrokes, maybe?  That sort of thing takes me a lot of time now on Excel, even though I'm using the sorter and whatnot.

                            Thanks,

                            Jack

                            Absorb the good, ignore the bad, weigh the ugly.

                            It's about number behavior.

                            Egos don't count.

                             

                            Dedicated to the memory of Big Loooser

                             

                              time*treat's avatar - radar

                              United States
                              Member #13130
                              March 30, 2005
                              2171 Posts
                              Offline
                              Posted: December 16, 2005, 12:03 pm - IP Logged

                              put them all in one column and histogram will do just that Wink

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