Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Vlookup in Macro (Excel 2002)

    Hi,
    Can anyone tell me what I've done wrong with the following code... it's not working.
    Thanks!
    Lana

    Sub testing()
    Range("A2") = Application.WorksheetFunction.VLookup(Range("B2"), Range("D2:E15"), 2, False)
    End Sub

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

    Re: Vlookup in Macro (Excel 2002)

    The code itself is correct. You'll get an error if the value of B2 cannot be found in D215 - because the 4th argument is False, VLookup looks for an exact match.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Vlookup in Macro (Excel 2002)

    Ahh yes... having it in the lookup range would help... thought I checked that 10 times, but I guess not! It works now! Thanks Hans!
    Lana

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Vlookup in Macro (Excel 2002)

    Okay, I got the simplified version of the code to work, now it won't work with my If's and then's... can you see what I've done wrong below... the vlookup line is almost at the bottom... I'm thinking it needs some kind of an "else" statement???
    Thanks!
    Lana

    Sub Translate()

    Dim lngRow As Long

    lngRow = 2
    Do While Sheets("ADP").Range("A" & lngRow) <> ""


    Sheets("Translation").Range("L" & lngRow) = Left(Sheets("Translation").Range("A" & lngRow), 2)

    If Sheets("Translation").Range("C" & lngRow) = "8000139" Then
    Sheets("Translation").Range("A" & lngRow) = "75SSCORP"
    End If

    If Sheets("Translation").Range("J" & lngRow) & Sheets("Translation").Range("I" & lngRow) = "47PM06PS" Then
    Sheets("Translation").Range("A" & lngRow) = "83BS"
    End If

    Sheets("Translation").Range("A" & lngRow) = Application.WorksheetFunction.VLookup(Sheets("Tran slation").Range("J" & lngRow), Sheets("vlookup").Range("A2:B65536"), 2, False)

    lngRow = lngRow + 1
    Loop

    End Sub

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

    Re: Vlookup in Macro (Excel 2002)

    I don't know what you want to accomplish, but perhaps this?

    Sub Translate()
    Dim lngRow As Long
    lngRow = 2
    Do While Sheets("ADP").Range("A" & lngRow) <> ""
    Sheets("Translation").Range("L" & lngRow) = _
    Left(Sheets("Translation").Range("A" & lngRow), 2)
    If Sheets("Translation").Range("C" & lngRow) = "8000139" Then
    Sheets("Translation").Range("A" & lngRow) = "75SSCORP"
    ElseIf Sheets("Translation").Range("J" & lngRow) & _
    Sheets("Translation").Range("I" & lngRow) = "47PM06PS" Then
    Sheets("Translation").Range("A" & lngRow) = "83BS"
    Else
    Sheets("Translation").Range("A" & lngRow) = _
    Application.WorksheetFunction.VLookup( _
    Sheets("Translation").Range("J" & lngRow), _
    Sheets("vlookup").Range("A2:B65536"), 2, False)
    End If
    lngRow = lngRow + 1
    Loop
    End Sub

  6. #6
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Vlookup in Macro (Excel 2002)

    This makes sense... the elseif was a new thing for me. I like it! I was doing an End If after every IF... of course, yours makes perfect sense and works great! I've learned yet another thing to add to my VBA knowledge base!
    Thanks Hans!
    Lana

Posting Permissions

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