Welcome Guest
Log In | Register )
You last visited January 18, 2017, 9:18 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 2 of 5
PrintE-mailLink
Avatar
New Mexico
United States
Member #12305
March 10, 2005
2984 Posts
Offline
Posted: December 16, 2005, 12:11 pm - IP Logged

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

 

    Avatar
    New Mexico
    United States
    Member #12305
    March 10, 2005
    2984 Posts
    Offline
    Posted: December 16, 2005, 1:09 pm - IP Logged

    I'm going to bite the bullet and appear exactly as stupid as I actually am.

    I've been trying to find Histogram.... a function.... anywhere.

    I looked in help and it tells all about communicating with the dialogue box.

    But I can't find a dialogue box, can't find anything about Histograms, how to start them without finding some dialogue box.

    I have Excel 97.  Is it possible Histogram was some function added later than that?

    I've looked through tools, macros, data, views, formats, can't find any reference to it except in HELP, which doesn't appear to help.

    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

     

      powerplayer's avatar - Lottery-022.jpg

      United States
      Member #17834
      June 28, 2005
      2083 Posts
      Offline
      Posted: December 16, 2005, 1:14 pm - IP Logged

      I'm going to bite the bullet and appear exactly as stupid as I actually am.

      I've been trying to find Histogram.... a function.... anywhere.

      I looked in help and it tells all about communicating with the dialogue box.

      But I can't find a dialogue box, can't find anything about Histograms, how to start them without finding some dialogue box.

      I have Excel 97.  Is it possible Histogram was some function added later than that?

      I've looked through tools, macros, data, views, formats, can't find any reference to it except in HELP, which doesn't appear to help.

      Thanks,

      Jack

      Rip Snorter

      It is a add-in item in excel.

      Go to tools add-ins. Then choose anylasis tools.

      I have excel 2002 so I don't know if this was a feature in 97 but, since it's add in I'm sure you can get it off of MS site since most new items are usually backwards compatiable.

       

      Powerplayer

      Good luck to everyone!!!

        Avatar
        New Mexico
        United States
        Member #12305
        March 10, 2005
        2984 Posts
        Offline
        Posted: December 16, 2005, 1:20 pm - IP Logged

        Got it.  Thanks powerplayer.

        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

         

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

          speaking of add-ins... there is one called "Solver" just waiting to be put into the number chase...

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

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

            Solver's not one that shows up among my add-ins on 'Excel 97.  Might be it was added sometime later.

            Thanks for the info hypersoniq.

            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

             

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

              Solver's not one that shows up among my add-ins on 'Excel 97.  Might be it was added sometime later.

              Thanks for the info hypersoniq.

              Jack

              it should be in excel '97

              there is some issue with seeing all of the add-in's in '97 that is addressed here

              http://support.microsoft.com/kb/157768/en-us

               

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

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

                Thanks.  Going there now.

                J

                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

                 

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

                  Found this on www.OzGrid.com

                  Trying to get around the conditional format limit of 3 with this VBA---Why doesn't it not work?   

                   Private Sub Worksheet_Change

                  Dim icolor As Integer
                      If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
                          Select Case Target
                          Case 1 To 5
                              icolor = 6
                          Case 6 To 10
                              icolor = 12
                          Case 11 To 15
                              icolor = 7
                          Case 16 To 20
                              icolor = 53
                          Case 21 To 25
                              icolor = 15
                          Case 26 To 30
                              icolor = 42
                          Case Else
                              'Whatever
                          End Select
                          Target.Interior.ColorIndex = icolor
                      End If
                     
                  End Sub

                  L ttaL   T

                    time*treat's avatar - radar

                    United States
                    Member #13130
                    March 30, 2005
                    2171 Posts
                    Offline
                    Posted: December 17, 2005, 1:23 pm - IP Logged

                    Most sites that have code on them figure that you have "outgrown" the basic books and are looking to do something more esoteric. For the sake of space they leave out the 'dim' and 'option' statements. <-- in longer code, your var decs, alone, can take up pages. They figure you know to put that in. I put it in when I post here if the code is short enough, or if it is something I have written already.

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

                      you are missing the bold red part, add it and it works just fine (but only when you enter data in the range that falls within the criteria)...

                      Private Sub Worksheet_Change(ByVal Target As Range)
                      Dim icolor As Integer

                          If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
                              Select Case Target
                                  Case 1 To 5
                                      icolor = 6
                                  Case 6 To 10
                                      icolor = 12
                                  Case 11 To 15
                                      icolor = 7
                                  Case 16 To 20
                                      icolor = 53
                                  Case 21 To 25
                                      icolor = 15
                                  Case 26 To 30
                                      icolor = 42
                                  Case Else
                                      'Whatever
                              End Select
                             
                              Target.Interior.ColorIndex = icolor
                          End If

                      End Sub

                      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 17, 2005, 4:25 pm - IP Logged

                        the problem is it doesn't automatically update when you make changes, you have to re-enter the data... and copy/paste brings up a runtime error.

                        here's a variation that highlights "v-trac" numbers in the A1:A10 range

                        Private Sub Worksheet_Change(ByVal Target As Range)
                        Dim icolor As Integer

                            If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
                                Select Case Target
                                    Case 0
                                        icolor = 2
                                    Case 1
                                        icolor = 3
                                    Case 2
                                        icolor = 4
                                    Case 3
                                        icolor = 5
                                    Case 4
                                        icolor = 6
                                    Case 5
                                        icolor = 2
                                    Case 6
                                        icolor = 3
                                    Case 7
                                        icolor = 4
                                    Case 8
                                        icolor = 5
                                    Case 9
                                        icolor = 6
                                    Case Else
                                        'Whatever
                                End Select
                                Target.Interior.ColorIndex = icolor
                            End If

                        End Sub

                         

                        same refresh/update problem...

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

                          time*treat's avatar - radar

                          United States
                          Member #13130
                          March 30, 2005
                          2171 Posts
                          Offline
                          Posted: December 17, 2005, 6:40 pm - IP Logged

                          I'm using a 'worksheet_activate' in one of my macros, too. When I want to update that sheet, I switch to any other sheet in that workbook, then switch back.

                          I also SET an OBJECT as the name of the workbook & sheet I'm using, then I can refer to that object name for the operation.

                          In effect, I am always using filename-sheetname-cells. That is a little more trouble than just using range or cells, but it lets you call your routine from anywhere; you may have noticed that using just cells or range that if you are not on the correct sheet, then you may get an error in a macro that was just fine a minute ago. There is no option to "sort of" describe what (you) want it to do & it figure it out for (you). This is a powerful application, but it still needs exact instruction. Maybe M$ is working on a mind-reader version for the next upgradeDanceavailable only to those who authenticate their copy.

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

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

                            lottaloot or hyper I have a pick 4 draws for Mass. going back to 1976. would either of you have an excel spreadsheet that would help selecting future pics after entering all these draws to any advantage. please help if yes. I have excel 2000.Thanks If it had filters it would be great.

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

                              Sorry Retxx, I don't deal with the Pick 4 too much. 

                              I have a worksheet that was given to me by SirMetro a while back--but that's it. 

                              L ttaL   T