CODEWITHSUNDEEP
vbaexcelvlookup
sam
sam

Reputation: 955

VLOOKUP returns 0 for blank cells

I have a data in SheetW like 

   A       B      C     D

 1 EID    Y/N

 2 1001    n
 3 1004    n
 4 1005    n
 5 1006    y
 6 1009    n
 7 1006    y
 8 1007    n
 9 1008    y    
10 1010

I'm using VLOOKUP in other sheetYN to fill Y/N based on above table, using vba vlookup

My vba code 

    With Sheets("sheetYN")
        LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With


    For X = 2 To LastRow2
    wsSheet1.Cells(X, 2).Formula = 
    "=VLOOKUP(A" & X & ",'SheetW'!$A$2:$B$321,2,FALSE)" 
   Next X

And the result looks like below. But empty *EID*s are filling with 0. How to display Y/N as blank instead of 0 if EID is blank 

 1 EID    Y/N
 2         0
 3 1001    n
 4         0
 5 1004    n
 6         0
 7 1005    n
 8         0
 9 1006    y
10         0
11 1009    n
12 1006    y
13 1007    n
14 1008    y    
15 1010    0

Upvotes: 0

Views: 1083

Answers (2)

pnuts
pnuts

Reputation: 59475

Easiest by way of a quick fix may be merely to accept the output 'as is' and format ColumnB with something like ;;;General, effectively hiding the cell's contents.

An alternative quick fix would be to trap the errors arising from failed lookups (ie #N/A results rather than 0) within the existing formula, by changing it to: 

wsSheet1.Cells(X, 2).Formula = "=iferror(VLOOKUP(A" & X & ",'SheetW'!$A$2:$B$321,2,FALSE),"""")"

so where there are no values to be looked up the formula displays nothing.

Upvotes: 0

Kory
Kory

Reputation: 318

Simple If statement should do the trick. Check if the cell next to you is blank. If true, then "" if false then have your formula.

Upvotes: 1

Related Questions