Welcome Guest
Log In | Register )
You last visited December 2, 2016, 7:21 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 3 of 5
PrintE-mailLink
lottaloot's avatar - AvatarZ56
Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
Posted: December 17, 2005, 9:52 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

It seemed to me that when I tried that way earlier, I got some sort of error code & had to remove that--but I will try again. 

 

Thanks

L ttaL   T

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

    It worked this time.

    Thanks Hyper Big Smile

    L ttaL   T

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

      retxx: I have nothing that actually worked yet. I am getting more and more into the 3 and 4 digit arena so maybe if I find something with PA's data that looks promising... but so far they either fail backtesting or the backtest formulas get so complex that it takes the sheet 20 minutes to recalculate on a Pentium4 :-(

      if you want some filters, you could send the sheet and I could whip up v-tracs or Odd/Even or any other filter you want, but as far as prediction goes, I am not very good at that... yet.

      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 18, 2005, 7:51 am - IP Logged

        These syntax errors can be really subtle to the human eye. Logic errors are even worse. I once spent the better part of 24hrs trying to figure out why I was getting an error in (previously perfectly working) macro B after I made a change in macro A. Answer; I had misspelled the argument name by one letter, so the value being "picked up" was 0, the default, not the value I was trying to pass.

        Get used to making intermediate printouts as these things get more complex.

        Heh, after awhile the homepage will say

        • forums
        • predictions
        • news
        • results
        • tutorials/classes
        it seems. Cool

        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 18, 2005, 9:51 am - IP Logged

          I am down with tutorial classes being  listed on the homepage.  Cool 

          L ttaL   T

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

            MINVERSE function returns the inverse matrix of matrix.

            • If matrix cannot be inverted, MINVERSE returns #NUM! error.
            • If matrix does not contain equal number of columns and rows, MINVERSE returns #VALUE! error.
            • This function is Excel compatible.

             

            More indepth explaination of this function, anyone? 

            L ttaL   T

              time*treat's avatar - radar

              United States
              Member #13130
              March 30, 2005
              2171 Posts
              Offline
              Posted: December 18, 2005, 7:37 pm - IP Logged

              matrices and their inverse are used, among other things, to solve simultaneous equations like

              6x + 3y = 66 while 7x - 2y = 11. These two equations each form a line, but intersect at one point ~ the solution.

              this one is easy enough to do on paper but things get tough with, say 5 variables. If all of the equations in the set don't 'fit' then the error message is a way of saying one or more of the 'lines' don't intersect with the others.

               

               

              for those of you playing at home, x = 5 & y = 12

              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
                3582 Posts
                Offline
                Posted: December 18, 2005, 9:46 pm - IP Logged

                lottaloot knowing you are versed on excel please help with this question. On my spreadsheet which is filled with past draw numbers I don't know how to continue adding current numbers so they line up at the top cell. need step by step if you would. want to add the date and draw. will need to do it each day. i have excel 2000. Thanks

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

                  Are you refering to the one that I sent you???

                  If so, Sirmetro set it up to where the formulas were housed only on row 2.  In order to move them down, you must click on A2, hit (Ctrl+Shift+the right arrow key) then (Ctrl+ Shift+down arrow key)

                  Right click-copy & move down only one row at a time. 

                  After a while, you will need to copy the sheet with the exception of row 2 & special paste only the values.

                  There is a macro that you can run, that will create some sort of recommended plays--I think; but I erased the sheet from my computer after I sent it to you so I can't pull it up. 

                  L ttaL   T

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

                    would you like me to mail it to you so you can see it?

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

                      I always sort ascending so the oldest draws are on top and I just add new ones to the bottom of the sheet, I can usually just autofill the formulas down and as long as I anchor them properly they just update along with the new data

                       

                      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 19, 2005, 11:36 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

                        L ttaL   T

                          time*treat's avatar - radar

                          United States
                          Member #13130
                          March 30, 2005
                          2171 Posts
                          Offline
                          Posted: December 19, 2005, 7:08 pm - IP Logged

                          lotta,

                          where did this one come from? 

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

                            looks like it takes as input a starting date and an ending date, then counts the days (rows) between the start and stop date...

                            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 19, 2005, 10:24 pm - IP Logged

                              lotta,

                              where did this one come from? 

                              http://www.mindspring.com/~tflynn/excelvba.html

                              L ttaL   T