Consulting

Results 1 to 14 of 14

Thread: VLookup VBA Code & Elseif troubles

  1. #1

    Question VLookup VBA Code & Elseif troubles

    Hi everyone,

    Thanks in advance for those who'll help me. I'll go staight to my problem: I have a list of ISIN and i'm writing a code to update all the date of those ISINs in the following columns. This data is found in another workbook (called book2 in the code). My problem is that when the ISIN is not found in "book2" the code returns the value "T+1" instead of leaving the data that was already there. What i would like the code to do is when the ISIN in book1 is not found in book2 then leave the data that was already there in book1. Find attached below the code I'm having problems with. If someone needs further explanation let me know please. Thanks in advance guys!


    Dim i As Integer
    Dim j As Integer
    Dim book1 As Workbook
    Dim book2 As Workbook
    
    
    Set book1 = Workbooks("Proves Dades Operatives")
    Set book2 = Workbooks("R71 1i2")
    
    
    i = 2
    
    
    Do While Cells(i, 1).Value <> ""
    
    
     If Application.WorksheetFunction.VLookup(book1.Sheets("Hoja1").Cells(i, 1), book2.Sheets("Hoja3").Range("A:H"), 7, 0) = 2 And Application.WorksheetFunction.VLookup(book1.Sheets("Hoja1").Cells(i, 1), book2.Sheets("Hoja3").Range("A:H"), 8, 0) = 1 Then
      Cells(i, 5) = "T+1"
     ElseIf Application.WorksheetFunction.VLookup(book1.Sheets("Hoja1").Cells(i, 1), book2.Sheets("Hoja3").Range("A:H"), 7, 0) = 0 And Application.WorksheetFunction.VLookup(book1.Sheets("Hoja1").Cells(i, 1), book2.Sheets("Hoja3").Range("A:H"), 8, 0) = 0 Then
      Cells(i, 5) = "T+0"
     ElseIf Application.WorksheetFunction.VLookup(book1.Sheets("Hoja1").Cells(i, 1), book2.Sheets("Hoja3").Range("A:H"), 7, 0) = 1 And Application.WorksheetFunction.VLookup(book1.Sheets("Hoja1").Cells(i, 1), book2.Sheets("Hoja3").Range("A:H"), 8, 0) = 1 Then
      Cells(i, 5) = "T-1"
     ElseIf Application.WorksheetFunction.VLookup(book1.Sheets("Hoja1").Cells(i, 1), book2.Sheets("Hoja3").Range("A:H"), 7, 0) = 1 And Application.WorksheetFunction.VLookup(book1.Sheets("Hoja1").Cells(i, 1), book2.Sheets("Hoja3").Range("A:H"), 8, 0) = 2 Then
      Cells(i, 5) = "T-2"
     ElseIf Application.WorksheetFunction.VLookup(book1.Sheets("Hoja1").Cells(i, 1), book2.Sheets("Hoja3").Range("A:H"), 7, 0) = 1 And Application.WorksheetFunction.VLookup(book1.Sheets("Hoja1").Cells(i, 1), book2.Sheets("Hoja3").Range("A:H"), 8, 0) = 3 Then
      Cells(i, 5) = "T-3"
         
    End If
    
    
        i = i + 1
     
     On Error Resume Next
    
    
    Loop

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    the OnError Resume Next line is your problem.
    I've made your code a little easier to read and made a minor adjustment. Can you test it for me to see if it still behaves in the same way. If an error pops up, tell me what the error says and what line it occurs on.
    Dim j As Integer
    Dim book1 As Workbook
    Dim book2 As Workbook
    Dim sht1 As Worksheet, sht2 As Worksheet, x, y
    
    Set book1 = Workbooks("Proves Dades Operatives")
    Set book2 = Workbooks("R71 1i2")
    Set sht1 = book1.Sheets("Hoja1")
    Set sht2 = book2.Sheets("Hoja3")
    i = 2
    
    Do While Cells(i, 1).Value <> ""
      x = Application.VLookup(sht1.Cells(i, 1), sht2.Range("A:H"), 7, 0)
      y = Application.VLookup(sht1.Cells(i, 1), sht2.Range("A:H"), 8, 0)
    
      If x = 2 And y = 1 Then
        Cells(i, 5) = "T+1"
      ElseIf x = 0 And y = 0 Then
        Cells(i, 5) = "T+0"
      ElseIf x = 1 And y = 1 Then
        Cells(i, 5) = "T-1"
      ElseIf x = 1 And y = 2 Then
        Cells(i, 5) = "T-2"
      ElseIf x = 1 And y = 3 Then
        Cells(i, 5) = "T-3"
      End If
      i = i + 1
     
     ' On Error Resume Next
    Loop
    You can also test the following.
    Dim i As Integer
    Dim j As Integer
    Dim book1 As Workbook
    Dim book2 As Workbook
    Dim sht1 As Worksheet, sht2 As Worksheet, x, y
    
    Set book1 = Workbooks("Proves Dades Operatives")
    Set book2 = Workbooks("R71 1i2")
    Set sht1 = book1.Sheets("Hoja1")
    Set sht2 = book2.Sheets("Hoja3")
    i = 2
    
    Do While Cells(i, 1).Value <> ""
      x = Application.VLookup(sht1.Cells(i, 1), sht2.Range("A:H"), 7, 0)
      y = Application.VLookup(sht1.Cells(i, 1), sht2.Range("A:H"), 8, 0)
    
      Select Case True
        Case x = 2 And y = 1: Cells(i, 5) = "T+1"
        Case x = 0 And y = 0: Cells(i, 5) = "T+0"
        Case x = 1 And y = 1: Cells(i, 5) = "T-1"
        Case x = 1 And y = 2: Cells(i, 5) = "T-2"
        Case x = 1 And y = 3: Cells(i, 5) = "T-3"
      End Select
      i = i + 1
    Loop
    This code should NOT require the On Error Resume Next line at all.
    Last edited by p45cal; 07-09-2019 at 02:28 PM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    Hi p45cal, thanks for your reply, it helps me improve to see better and cleaner ways to write the code. Unfortunatelly it keeps returning T+1 on the ISINs that don't appear on book2. How is it possible that tehe code shows T+1 when the value is not even on book2, it doesn't make sense to me and i don't understand it.
    On other columns of the book1 i have some code write in with a simple vlookup function and in these cases if the value doesn't appear on book2 it skips the cell and leaves the value that was already in it. To give you and example I'll show you a part of the code where if the ISIN it's not found on book2 it leaves the value that was in the cell untouched (This works completely find). Thanks in advance again!

    'Cut Off
    
    
    i = 2
    
    
    Do While Cells(i, 1).Value <> ""
    
    
     Cells(i, 11) = Application.WorksheetFunction.VLookup(book1.Sheets("Hoja1").Cells(i, 1), book2.Sheets("Hoja3").Range("A:Q"), 17, 0)
        i = i + 1
     
     On Error Resume Next
    
    
    Loop

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    So you're confirming that it DOES behave the same way - Good.
    Now, take out the On Error Resume Next line and run it again and tell me what the error report says and copy the line that is highlighted in your next post.

    It is this On Error Resume Next line which is causing all your problems; take it out. Then we'll deal with the reported errors.

    Did you try my second snippet?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    Hi,

    Yes, I tried everything: taking out "On error resume next" and the 2 codes you posted. The code doesn't give any kind of error per se. The problem is that when the value I'm looking for (from book1) doesn't appear in book2 it returns T+1. What i would like the code to do is if the value is not found in book2 leave the cell in book1 untouched.

    In book1 i have some ISINs with relevant info that is taking from book2 (which is the main database). The problem is that some of these ISINs don't appear in book2 so the data I need to fill in I just look it up in other sources and type it in book1. But when I run the macro, these data that I typed in because it wasn't in book2 just gets overwritten (when it shouldn't get overwritten because it doesn't appear in book2). I feel it's a mess of explanation, but if you need more info or I didn't make myself clear let me know please.

    Ps. I don't know if it could be this, but problem only occurs when the code has more than one vlookup function

    Thanks again!

  6. #6
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    p45cal, I would write the second code a little differently. First, I would check if X or Y (or both) contain an error. Then, Select Case performed only when there is no error.
    Do While Cells(i, 1).Value <> ""
        With Application
          x = .VLookup(sht1.Cells(i, 1), sht2.Range("A:H"), 7, 0)
          y = .VLookup(sht1.Cells(i, 1), sht2.Range("A:H"), 8, 0)
        End With
          
          If Not IsError(x) Then
            Select Case True
              Case x = 2 And y = 1: Cells(i, 5) = "T+1"
              Case x = 0 And y = 0: Cells(i, 5) = "T+0"
              Case x = 1 And y = 1: Cells(i, 5) = "T-1"
              Case x = 1 And y = 2: Cells(i, 5) = "T-2"
              Case x = 1 And y = 3: Cells(i, 5) = "T-3"
            End Select
        End If
        
        i = i + 1
    Loop
    noob93, You must know that there is a difference in error handling (when the sheet function returns an error), depending on the context in which you call this function.
    If it do not find the result:
    - Application.WorksheetFunction.VLookup creates a runtime error,
    - Application.VLookup returns a sheet error (e.g., #N/A!)

    I think Charles Pearson will explain it enough http://www.cpearson.com/Excel/Callin...ionsInVBA.aspx

    Artik
    Last edited by Artik; 07-10-2019 at 06:26 AM.

  7. #7
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Artik, I agree with you, and indeed it might be faster (I might even check for errors in x and y) but it shouldn't be necessary. x and y are always assigned something at each loop so I can't see that it is happening.
    So noob93, there are many unknowns so I'd like to see more; can you attach a couple of files with your full code for this macro and any other macros/functions it might call as close to your real life files as you can?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #8
    Hi p45cal,

    I'm sending attached the workbook I'm working with (I'm having troubles to upload book2 because is too heavy. I got it to 2,2MB but still not uploading). In the book "Proves Dades Operatives" I deleted a sheet with delicate information. That sheet is where the info for "Fons Act" comes from (this sheet is used to check if the ISINs we have in portfolio are in Hoja1 with all the info updated).

    In the sheet called "Hoja1" in "Proves Dades Operatives" you'll find in column V a check that tells you if the ISIN is updated ("Actualitzat" in Catalan). The code returns "SI" if hte ISIN is in book2 (R71 1i2) and is able to update the info from there. It will return "NO" when the ISIN is not found in book2. I have troubles with most of the ISINs that don't appear in book2 (what we've been talking until now).

    For example, for the ISIN LU1808486812 you'll find informed T+1 in column E and T+3 in column F. After running the macro you can see a T+1 in column E and T+0 in F (this will happen to all ISINs not updated and/or starting by "ES", which they get updated through other sources and typed in manually in the file)

    Do you guys have any way to receive the reamining file (book2 "R71 1i2"). Maybe email trough private message? Let me know please.

    If you need further information or explanations don't hesitate to ask me.

    Thanks again for your help, really appreciate it!
    Attached Files Attached Files

  9. #9
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    You can put the file on a file sharing site (there are many) and provide a link to it in a message.
    [There may be a lower limit of the number of posts you need to have posted here before you can include links here - I'm not sure. At several forums it's 5 posts minimum - so if you come across this, just add sufficient short posts or include the link but miss off the https: or http: from the beginning of the link so that the system here doesn't recognise it as a link]
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  10. #10
    Try if this works: //drive.google.com/drive/folders/1Xewo57E4VpzNdcKBLlUG9AUnnfF1d3_c?usp=sharing

  11. #11
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Yes, it worked.
    Which sheet in which workbook is being updated by this macro?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  12. #12
    The sheet that gets updated by the macro is "Hoja1" in workbook "Proves Dades Operatives". Workbook R71 1i2 is sent to us upadeted weekly

    Rgrds,

  13. #13
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    The attached has your macro re-written.
    If the code in column A is not found on the other sheets nothing changes.
    If it is found then cells are updated, including putting "???" in cells where values don't match any of your combinations (there were none in your sample file).
    Note that column C is not updated at all as is the case with several other columns - be careful.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  14. #14
    Hi p45cal,

    Thanks for your help, it's been really useful. On friday afternoon I finally achieved to make the code work. I moved the check code from column V to the beginning to do: If ISIN is column A it's in book 2 run the macro, if not go to next ISIN. I attach below a sample of the code. Thanks again for everything!

    'Check Act
    
    
    i = 2
    
    
    Do While Cells(i, 1).Value <> ""
    
    
     If Application.WorksheetFunction.VLookup(book1.Sheets("Hoja1").Cells(i, 1), book2.Sheets("Hoja3").Range("A:S"), 19, 0) = """" Then
      Cells(i, 22) = "NO"
     Else
      Cells(i, 22) = "SI"
     End If
    
    
    i = i + 1
    
    
    On Error Resume Next
    Loop
    
    'Preavis Compres
    
    
    i = 2
    x = ActiveSheet.UsedRange.Rows.Count
    
    
    
    
    Do While Cells(i, 1).Value <> ""
     For i = 2 To x
      If Cells(i, 22) = "SI" Then
    
    
       If Application.WorksheetFunction.VLookup(book1.Sheets("Hoja1").Cells(i, 1), book2.Sheets("Hoja3").Range("A:H"), 7, 0) = 2 And Application.WorksheetFunction.VLookup(book1.Sheets("Hoja1").Cells(i, 1), book2.Sheets("Hoja3").Range("A:H"), 8, 0) = 1 Then
        Cells(i, 5) = "T+1"
       ElseIf Application.WorksheetFunction.VLookup(book1.Sheets("Hoja1").Cells(i, 1), book2.Sheets("Hoja3").Range("A:H"), 7, 0) = 0 And Application.WorksheetFunction.VLookup(book1.Sheets("Hoja1").Cells(i, 1), book2.Sheets("Hoja3").Range("A:H"), 8, 0) = 0 Then
        Cells(i, 5) = "T+0"
       ElseIf Application.WorksheetFunction.VLookup(book1.Sheets("Hoja1").Cells(i, 1), book2.Sheets("Hoja3").Range("A:H"), 7, 0) = 1 And Application.WorksheetFunction.VLookup(book1.Sheets("Hoja1").Cells(i, 1), book2.Sheets("Hoja3").Range("A:H"), 8, 0) = 1 Then
        Cells(i, 5) = "T-1"
       ElseIf Application.WorksheetFunction.VLookup(book1.Sheets("Hoja1").Cells(i, 1), book2.Sheets("Hoja3").Range("A:H"), 7, 0) = 1 And Application.WorksheetFunction.VLookup(book1.Sheets("Hoja1").Cells(i, 1), book2.Sheets("Hoja3").Range("A:H"), 8, 0) = 2 Then
        Cells(i, 5) = "T-2"
       ElseIf Application.WorksheetFunction.VLookup(book1.Sheets("Hoja1").Cells(i, 1), book2.Sheets("Hoja3").Range("A:H"), 7, 0) = 1 And Application.WorksheetFunction.VLookup(book1.Sheets("Hoja1").Cells(i, 1), book2.Sheets("Hoja3").Range("A:H"), 8, 0) = 3 Then
        Cells(i, 5) = "T-3"
         
       End If
     
      End If
     Next i
    i = i + 1
         
    
    
     On Error Resume Next

Tags for this Thread

Posting Permissions

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