Results 1 to 11 of 11
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    E_MAIL AND MATCHIN... (2000 sr-1)

    I use the macto CMDINVIA_CORPORATE for many my application...
    Now, this macro send e-mail a user in column C with various notice, my prob:
    Inested to send the value of variable to a single user is possible:
    Matching the value in range H:N of sheet CORPORATE with the range column H of GAF and if is present in the and send all line of sheet GAF to the user in column D do until the value of matching matching finished...

    Finished the matching for user OI34573, use the same tecnique with OI35475, ecc...

    i have attached a report to use in standard mode for each user...

    Hope understand me.
    Naturally if have other suggestion....

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: E_MAIL AND MATCHIN... (2000 sr-1)

    I'm sorry, Sal, you're asking too much here. I suggest that you take a look at previous threads where you got help on matching rows in different worksheets.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: E_MAIL AND MATCHIN... (2000 sr-1)

    Hans, no prob...
    But is possible a little piece of code to matching only the to column...
    Example:
    matching value and when one of this is found show msgbox "Found"...

    In this case i arrange the code to send email, i hope:-)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: E_MAIL AND MATCHIN... (2000 sr-1)

    Sal, you have received help on finding matches in columns many times in the past, so you should be able to adapt the code you already have.

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: E_MAIL AND MATCHIN... (2000 sr-1)

    First steep:
    correct this:
    Sub TROVA()
    Dim ULTIMA As Long
    Dim I As Long
    ULTIMA = Worksheets("GAF").Cells(Rows.COUNT, "A").End(xlUp).Row
    For I = ULTIMA To 2 Step -1
    If IsError(Application.Match(Cells(I, "H").Value, Worksheets("CORPORATE").Range("H2:N2"), 0)) Then
    TEST = Worksheets("GAF").Cells(I, "H").Value
    End If
    Next I
    End Sub

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: E_MAIL AND MATCHIN... (2000 sr-1)

    I would use the Find method instead of the worksheet function Match here:

    Sub TROVA()
    Dim ULTIMA As Long
    Dim I As Long
    Dim TEST
    Dim rng As Range
    ULTIMA = Worksheets("GAF").Cells(Rows.Count, "A").End(xlUp).Row
    For I = ULTIMA To 2 Step -1
    Set rng = Worksheets("CORPORATE").Range("H2:N2"). _
    Find(What:=Worksheets("GAF").Cells(I, "H"), LookAt:=xlWhole)
    If Not rng Is Nothing Then
    TEST = Worksheets("GAF").Cells(I, "H").Value
    End If
    Next I
    End Sub

    I'm not sure what exactly you want to do here, you may have to change

    If Not rng Is Nothing Then

    to

    If rng Is Nothing Then

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: E_MAIL AND MATCHIN... (2000 sr-1)

    SIMPLY, great!!!

    Hummm....
    But when i set the range H:N and in this range is present a blank cell the macro found blank in H of GAF...
    Is possible to set the range H:N, similar H2:Nto the last filled cell...

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: E_MAIL AND MATCHIN... (2000 sr-1)

    You can do it like this:

    Sub TROVA()
    Dim ULTIMA As Long
    Dim I As Long
    Dim TEST
    Dim rng As Range
    Dim LastCol As Long
    ULTIMA = Worksheets("GAF").Cells(Rows.Count, "A").End(xlUp).Row
    LastCol = Worksheets("CORPORATE").Cells(2, Columns.Count).End(xlToLeft).Column
    For I = ULTIMA To 2 Step -1
    Set rng = Worksheets("CORPORATE").Range("H2:N" & LastCol). _
    Find(What:=Worksheets("GAF").Cells(I, "H"), LookAt:=xlWhole)
    If Not rng Is Nothing Then
    TEST = Worksheets("GAF").Cells(I, "H").Value
    End If
    Next I
    End Sub

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: E_MAIL AND MATCHIN... (2000 sr-1)

    Hans sorry me...
    To test, i insert a "T" in column R of GAF...
    and the actual macro insert the test "T" in column R of GAF also when the cells are blank...
    and not when found in column H of gaf the value in range of CORPORATE...
    I want to insert "T" only if in column H of GAF are present the same value in rang H2:N2 of CORPORATE:-(
    In effect insert in column R the " T" of GAF only for 1,2,3,4,6,7,15
    Attache sheet CORPORATE and GAF


    Sub TROVA()
    Dim ULTIMA As Long
    Dim I As Long
    Dim TEST
    Dim RNG As Range
    Dim LASTCOL As Long
    Dim RIGA As String
    RIGA = 1

    ULTIMA = Worksheets("GAF").Cells(Rows.COUNT, "A").End(xlUp).Row
    LASTCOL = Worksheets("CORPORATE").Cells(2, Columns.COUNT).End(xlToLeft).Column

    For I = ULTIMA To 2 Step -1
    Set RNG = Worksheets("CORPORATE").Range("H2:N" & LASTCOL). _
    Find(What:=Worksheets("GAF").Cells(I, "H"), LookAt:=xlWhole)
    If Not RNG Is Nothing Then
    TEST = Worksheets("GAF").Cells(I, "H").Value
    Worksheets("GAF").Cells(I, "R") = "T"
    RIGA = RIGA + 1
    End If
    Next I

    End Sub

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: E_MAIL AND MATCHIN... (2000 sr-1)

    You can add a test that the cell is not blank:

    For I = ULTIMA To 2 Step -1
    If Not Worksheets("GAF").Cells(I, "H") = "" Then
    Set RNG = ...
    ...
    End If
    Next I

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: E_MAIL AND MATCHIN... (2000 sr-1)

    Tks...
    Solved a big Problem, for me naturally!
    Ths code work perfect!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •