VLookup VBA Code & Elseif troubles

[noob93]

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

[p45cal]

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.

[noob93]

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

[p45cal]

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?

[noob93]

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!

[Artik]

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

[p45cal]

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?

[noob93]

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!

[p45cal]

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]

[noob93]

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

[p45cal]

Yes, it worked.
Which sheet in which workbook is being updated by this macro?

[noob93]

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,

[p45cal]

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.

[noob93]

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