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

    Vlokup with 2 value in 2 different column (2000 s

    I ahve thsi piece of code, but not work...
    In effect sNotFound now is 4500-45788

    i wanto to find if in sheet ESSERE in column F joined& "-" & G the value of sNotFound

    in G of ESSERE i can have
    4500
    4500
    4501
    in F ican have
    4578
    4784
    14587


    While Not Sheets("TOTALE_1").Range("H" & RIGA) = ""

    sNotFound = Sheets("TOTALE_1").Range("H" & RIGA).Value

    Sheets("TOTALE_1").Range("IV" & RIGA).Value = Application.WorksheetFunction.VLookup(sNotFound, Sheets("ESSERE").Range(Sheets("ESSERE").Range("A2" ), Sheets("ESSERE").Range("A65536").End(xlUp)), 2, False)

    Wend

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

    Re: Vlokup with 2 value in 2 different column (2000 sr -1)

    Your VLOOKUP searches in column A of the ESSERE worksheet. Does column A contain the concatenated value of columns G and F?

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

    Re: Vlokup with 2 value in 2 different column (2000 sr -1)

    yes concatenated F G with "-",...
    attached file...

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

    Re: Vlokup with 2 value in 2 different column (2000 sr -1)

    You say that sNotFound is 4500-45788, and that
    <hr>in G of ESSERE i can have
    4500
    4500
    4501
    in F ican have
    4578
    4784
    14587<hr>
    So it seems that sNotFound corresponds to G & "-" & F, not to F & "-" & G. (The 45788 was a typing error, I assume)

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

    Re: Vlokup with 2 value in 2 different column (2000 sr -1)

    F & "-" & G
    SORRYYY....
    in F of ESSERE i can have
    4500
    4500
    4501
    in G ican have
    4578
    4784
    14587

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

    Re: Vlokup with 2 value in 2 different column (2000 sr -1)

    I hadn't seen that you had added an attachment while I was posting my first reply. The workbook you attached has nothing in column A, so VLOOKUP won't find anything.

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

    Re: Vlokup with 2 value in 2 different column (2000 sr -1)

    Maked this, but go in error...in line 25 of sheet ESSERE...
    Sub TEST2()

    Dim MIO_RANGE As Range
    Dim CELLA As Range
    Dim TEST As String
    Dim VALORE1 As String
    Dim VALORE2 As String

    TEST = ""
    Set MIO_RANGE = Range(Sheets("ESSERE").[H2], Sheets("ESSERE").[G65536].End(xlUp).Offset(0, 1))

    For Each CELLA In MIO_RANGE

    VALORE1 = CELLA.Offset(0, -2)
    VALORE2 = CELLA.Offset(0, -1)

    TEST = VALORE1 & "-" & VALORE2

    If Len(Application.WorksheetFunction.VLookup(TEST, Range(Worksheets("TOTALE_1").[H2], Worksheets("TOTALE_1").[H65536].End(xlUp)), 1, False)) > 0 Then
    CELLA = "TROVATO"
    Else
    CELLA = "NON TROVATO"
    End If
    Next CELLA

    End Sub

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

    Re: Vlokup with 2 value in 2 different column (2000 sr -1)

    Application.VLookup causes an error if the value is not found. Try this:

    Sub TEST2()
    Dim MIO_RANGE As Range
    Dim CELLA As Range
    Dim TEST As String
    Dim VALORE1 As String
    Dim VALORE2 As String
    Dim rngTotale As Range
    Dim rngFind As Range

    TEST = ""
    Set MIO_RANGE = Range(Sheets("ESSERE").<!t>[H2]<!/t>, Sheets("ESSERE").<!t>[G65536]<!/t>.End(xlUp).Offset(0, 1))
    Set rngTotale = Range(Worksheets("TOTALE_1").<!t>[H2]<!/t>, Worksheets("TOTALE_1").<!t>[H65536]<!/t>.End(xlUp))

    For Each CELLA In MIO_RANGE
    VALORE1 = CELLA.Offset(0, -2)
    VALORE2 = CELLA.Offset(0, -1)
    TEST = VALORE1 & "-" & VALORE2
    Set rngFind = rngTotale.Find(What:=TEST, LookIn:=xlValues, LookAt:=xlWhole)
    If rngFind Is Nothing Then
    CELLA = "NON TROVATO"
    Else
    CELLA = "TROVATO"
    End If
    Next CELLA
    End Sub

Posting Permissions

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