excel - Formula only if cell not empty -

i'm trying formula fill cell value, once, when cell empty. once filled want value stay is, though cells part of formula changed.

ie

c: =vlookup(cx,'a$1:b$3,2,false) 

originaly

a       b      c john    1      1 bob     4      4 jim     6      6 

after changing jim's b value

jim     10      6 

so want vlookup fetch value if 1 isn't there already. tried if, got circular reference error:

=if(c1= "", vlookup(c1,'a$1:b$3,2,false),c1) 

i think along lines want

• right-click sheet tab
• view code
• copy , paste in code below
• press alt & f11 excel

if enter 12 in b4 and a4 not blank then

1. if c4 empty, sety equal 12
2. if c4 has value, retained

code

private sub worksheet_change(byval target range) dim rng1 range dim rng2 range set rng1 = intersect(columns("b:b"), target) if rng1 nothing exit sub application.enableevents = false each rng2 in rng1 if rng2.offset(0, -1).value <> vbnullstring rng2.offset(0, 1).value = rng2.value next application.enableevents = true end sub