Welcome Guest
Log In | Register )
The time is now 12:03 am
You last visited July 24, 2014, 12:01 am
All times shown are
Eastern Time (GMT-5:00)

Excel help (dates and draws)

Topic closed. 33 replies. Last post 2 years ago by alchemist7.

Page 3 of 3
PrintE-mailLink
SergeM's avatar - alas
Economy class
Belgium
Member #123705
February 27, 2012
2724 Posts
Offline
Posted: October 29, 2012, 6:57 am - IP Logged

Sergio  I don't  play these games. glad you were able to correct my problems.  share so we can see the error of my ways.

Lurking

    Avatar

    United States
    Member #65711
    October 4, 2008
    30 Posts
    Offline
    Posted: November 1, 2012, 8:27 pm - IP Logged

    The bad thing about a macro it is generally writen for a specific purpose and a specific data set. if you can follow the code it can be modified, but you need to understand what the programmer was trying to do.  If you understand that you would would probably just write your own.

    Alchemist7  if your data is set up like your example, and it probably is not then this macro will look for 4 values you place in row 1 col J-M and show any matches below. I have included many comments to make it easier to follow.

     

    Sub find_number()
    ' #'s data starts on line 2 col A-D date is in col F
    ' comb to look for is in line 1 col J-M this can also be stated as Column 10-13 numbers are usually easier to program
    ' where is the results going to be displayed.  I will choose line 3 starting at col J or 10
    ' do you want exact match or box match.  I am going to assume a box match
    Dim tn(10), dn(10) ' these array variables will let me easily check for a box match
    Range("j3:p5000").Select
        Selection.ClearContents ' clear this space
            Selection.Interior.ColorIndex = 0 ' remove coloring if any
    Range("I1").Select' moves cursor out of the way
    Ln = 3: cl = 10 'initial result display location
    For y = 10 To 13: a = Cells(1, y): tn(a) = tn(a) + 1: Next y ' target #'s to look for loaded in array tn()
    'set up a loop to look thru all of your data
    For x = 2 To 5000 ' data starts on line 2 and goes thru 5000 change as appropiate end # is not critical can be well beyond available data lines
    For y = 0 To 10: dn(y) = 0: Next y ' this clears the dn() array after every line checked
    For y = 1 To 4 ' four data elements
    If a = "" Then a = 5000: GoTo done ' terminate loop if out of data
    a = Cells(x, y): dn(a) = dn(a) + 1: Next y ' this draw "x" is now in array dn()ready to be compared to tn()
    GoSub compare 'a gosub is not an absolute requirement here but in my opinion makes the code less cluttered

    done:
    Next x

    Exit Sub

    compare: 'compare the two arrays and display whatever you choose
    m = 0 ' will be used to count # of matches. this clears the variable before each use
    For v = 0 To 9
    If tn(v) = dn(v) Then m = m + 1
    Next v ' at this point if all of the numbers are the same m will =10 order does not matter this macro looks for a box match
    If m = 10 Then GoSub display 'again convienance
    Return

    display: ' display whatever we want I have chosen to show all six elements
    Cells(Ln, cl) = Cells(x, 1): Cells(Ln, cl + 1) = Cells(x, 2): Cells(Ln, cl + 2) = Cells(x, 3): Cells(Ln, cl + 3) = Cells(x, 4)
    Cells(Ln, cl + 4) = Cells(x, 5): Cells(Ln, cl + 5) = Cells(x, 6)
    Ln = Ln + 1: cl = 10
    Return

    end sub

    Thanks everyone for all of your help!  I had some issues and being busy, I didn't have very much time to work on this until tonight! 

    Phileight,  thanks for the macro! It works great! With your comments it will help me tweak it to work on a larger section of lookup numbers. I'm going to work on it some more tonight and this weekend. I will post the finished results and the code for anyone else ( it probably won't be simple as yours, because loops give me alot of trouble).

     

    Again everyone THANKS!

    Alchemist

      Avatar

      United States
      Member #41846
      June 23, 2006
      272 Posts
      Offline
      Posted: November 2, 2012, 5:36 am - IP Logged

      happy to help.  I generally look in morn and eve if you get stuck.  p8

        Avatar

        United States
        Member #65711
        October 4, 2008
        30 Posts
        Offline
        Posted: November 27, 2012, 11:08 am - IP Logged

        happy to help.  I generally look in morn and eve if you get stuck.  p8

        I  know its a month later but I finally buckled down and worked on the code and got it to do what I wanted!  Thanks to phileight for the starting point and pointers! I will post the code below (its not as neat and organized as the original but it works for me).

         

         

         

        Sub dates()

        ' #'s data starts on line 2 col A-D date is in col F
        ' comb to look for is in line 1 col J-M this can also be stated as Column 10-13 numbers are usually easier to program
        ' where is the results going to be displayed.  I will choose line 3 starting at col J or 10
        ' do you want exact match or box match.  I am going to assume a box match
        Dim tn(10), dn(10) ' these array variables will let me easily check for a box match
        Range("l1:rdd5000").Select
            Selection.ClearContents ' clear this space
                Selection.Interior.ColorIndex = 0 ' remove coloring if any
        Range("I1").Select ' moves cursor out of the way
        cl = 13 'initial result display location
        For b = 2 To 100 'starting point of the look up numbers column
        Ln = 2
        For y = 8 To 11: a = Cells(b, y): tn(a) = tn(a) + 1: Next y ' target #'s to look for loaded in array tn()
        'set up a loop to look thru all of your data
        For x = 2 To 5000 ' data starts on line 2 and goes thru 5000 change as appropiate end # is not critical can be well beyond available data lines
        For y = 0 To 10: dn(y) = 0: Next y ' this clears the dn() array after every line checked
        For y = 1 To 4 ' four data elements
        If a = "" Then a = 5000: GoTo done ' terminate loop if out of data
        a = Cells(x, y): dn(a) = dn(a) + 1: Next y ' this draw "x" is now in array dn()ready to be compared to tn()
        GoSub compare 'a gosub is not an absolute requirement here but in my opinion makes the code less cluttered

        done:
        Next x
        For c = 0 To 10: tn(c) = 0: Next c
        cl = cl + 7
        Next b



        Exit Sub

        compare: 'compare the two arrays and display whatever you choose
        m = 0 ' will be used to count # of matches. this clears the variable before each use
        For v = 0 To 9
        If tn(v) = dn(v) Then m = m + 1
        Next v ' at this point if all of the numbers are the same m will =10 order does not matter this macro looks for a box match
        If m = 10 Then GoSub display 'again convienance
        Return

        display: ' display whatever we want I have chosen to show all six elements
        Cells(Ln, cl) = Cells(x, 1): Cells(Ln, cl + 1) = Cells(x, 2): Cells(Ln, cl + 2) = Cells(x, 3): Cells(Ln, cl + 3) = Cells(x, 4)
        Cells(Ln, cl + 4) = Cells(x, 5): Cells(Ln, cl + 5) = Cells(x, 6)
        Ln = Ln + 1: cl = cl
        Return

        End Sub