error in VBA lookup code

[prometheus]

solved


-----------

Hi

I attached a worksheet where my vba codes returns an NA sometimes.. I have no idea why

its the sub Sub oldhome_homev5() that gives the error,

what is should do, lookup the value on for example row 198 in column AR ( man city) and see:

if it can find that value in column AI,
if not lookup the value in column AJ,
if not lookup value in column AF and find it in column AI,
if not lookup the value of column af in column AJ,
if not put as a value 0

in this specific case on row 198 you have a value "201119 mancity" this value doenst exist in AI or AJ so it should lookup the value 201118man City instead.. which is indeed found in column AJ, row 179, yet somehow the vba codes returns an NA value..

i indicated the example in the worksheet with orange cells..

code is listed below

If Application.WorksheetFunction.CountIf(Range("ai:ai"), (Cells(12 + i, 31))) > 0 Then

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


ElseIf Application.WorksheetFunction.CountIf(Range("aj:aj"), (Cells(12 + i, 31))) > 0 Then
   
 Cells(12 + i, 37).Value = Application.VLookup(Cells(12 + i, 31), Sheets("data").Range("aj:aw"), 14, False)


ElseIf Application.WorksheetFunction.CountIf(Range("ai:ai"), (Cells(12 + i, 32))) > 0 Then


Cells(12 + i, 37).Value = Application.VLookup(Cells(12 + i, 32), Sheets("data").Range("ai:aw"), 12, False)
   
ElseIf Application.WorksheetFunction.CountIf(Range("aj:aj"), (Cells(12 + i, 32))) > 0 Then
   
 Cells(12 + i, 37).Value = Application.VLookup(Cells(12 + i, 31), Sheets("data").Range("aj:aw"), 14, False)


Else


Cells(12 + i, 37).Value = 0

correct calcsv2.xlsm

[Paul_Hossler]

Not sure what it is you're wating to do, but AE198

Cells(12 + i, 31).value = 201119Man City

which is not found


Capture.JPG