vlookup

[Forestq]

Hi,

how to change vlookup that instead for found value, put just "yes" ?

Dim rng, stullpath As String
rng = ws2.Range("A4:" & Range("B4").End(xlDown).Address).Address
    
sfullpath = "[" & ws2.Parent.Name & "]" & ws2.Name


ws1.Range("A1").Select
x_rows_vlookup = Application.WorksheetFunction.CountA(Columns(2))
x_rows_vlookup = x_rows_vlookup - 1
ws1.Range("C2").Select
                    
ActiveCell.Formula = "=IF(ISNA(VLOOKUP(B2,'" & sfullpath & "'!" & rng & ",1,0)),""NO"",VLOOKUP(B2,'" & sfullpath & "'!" & rng & ",1,0))"


Selection.AutoFill Destination:=Range("C2:C" & x_rows_vlookup), Type:=xlFillDefault
                    
Range("C2:C" & x_rows_vlookup).Select
Selection.Copy
Range("C2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

 

[Geoff Taylor]

You've got your IF(ISNA test to determine if the VLOOKUP is working. If it isn't working, put "No". If it is, put "YES". If I've understood you correctly, couldn't you just completely remove the second VLOOKUP and replace it with "YES"?

 

[Forestq]

did it as you wrote and it' fine, thanks Geoff Taylor!