PDA

View Full Version : iserror and lookup not working combined



prometheus
04-21-2016, 10:09 AM
hi

the below code is not working, the second part of the sub works fine, somehow the first part return NA errors in my sheet, which shouldnt happen



actually im just trying to do the following: see if a value in cell XX can be found in column (YY) ,

if found in that column do the vlookup " Cells(21 + i, 37).Value = Application.VLookup(Cells(21 + i, 31), Sheets("data").Range("ai:aw"), 12, False)

if it is not found in column (YY) it means it should be found in column (zz)

so then lookup " Cells(21 + i, 37).Value = Application.VLookup(Cells(21 + i, 31), Sheets("data").Range("aj:aw"), 11, False) "

---- note : this is the first step of the program, it is a bit more complex , because if the value Cells(21 + i, 31) is not found in both column YY or ZZ it should in fact lookup the (21 + i, 31) in the same columns , then perform the same lookups...


any help on either the first part of the sub or the full sub?


edit i added the sheet so maybe some would be kind enough to help me :)




Sub oldhome_home()





Dim i As Integer
i = 1



' nota ik ga hier de sub nesten dus bij NA eerst kijken naar -2, dan naar de -1


If IsError(Application.VLookup(Cells(21 + i, 31), Sheets("data").Range("ai:aw"), 12, False)) Then


Do While Cells(21 + i, 31).Value <> ""
Cells(21 + i, 37).Value = Application.VLookup(Cells(21 + i, 31), Sheets("data").Range("aj:aw"), 11, False)


i = i + 1
Loop


Else


Do While Cells(21 + i, 31).Value <> ""
Cells(21 + i, 37).Value = Application.VLookup(Cells(21 + i, 31), Sheets("data").Range("ai:aw"), 12, False)




i = i + 1

Loop




End If
End Sub

Bob Phillips
04-22-2016, 06:29 AM
Why don't you tell us in words what you are trying to do, rather than with your code which doesn't work as you say.