CODEWITHSUNDEEP
excelexcel-formulavba
Lukasz
Lukasz

Reputation: 599

Formula only if cell not empty

I'm trying to do a formula that will fill the cell value, but only once, when the cell is empty. Once filled I want the value to stay as is, even though cells that are part of the 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 I want the VLOOKUP to fetch a value only if one isn't there already. I tried to do it with an IF, but I got a circular reference error:

=IF(C1= "", VLOOKUP(C1,'A$1:B$3,2,FALSE),C1)

Upvotes: 1

Views: 7138

Answers (2)

glh
glh

Reputation: 4972

I'm not completely sure of what you want but I think brettdj has the answer. Mine will change all empty cells in column C to a formula, to be modified to your situation, at the moment it just looks up the name and returns the column B value.

Please I know the VLOOKUP() is not required, but I wanted to show how to use it in this context.

Sub change_empty()

    Dim l As Long, r As Range
    l = Me.Cells.Find("*", , , , xlByRows, xlPrevious).Row
    Set r = Me.Range("C1").Resize(l).SpecialCells(xlCellTypeBlanks)

    r.FormulaR1C1 = "=VLOOKUP(RC1,C1:C2,2,FALSE)"

End Sub

Upvotes: 0

brettdj
brettdj

Reputation: 55682

I think this is along the lines you want

  • right-click your sheet tab
  • View Code
  • copy and paste in the code below
  • press & to get back to Excel

If you enter say 12 in B4 and A4 is not blank then

  1. If C4 is empty, it will be sety equal to 12
  2. if C4 has a value, it is retained

code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Intersect(Columns("B:B"), Target)
If rng1 Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each rng2 In rng1
If rng2.Offset(0, -1).Value <> vbNullString Then rng2.Offset(0, 1).Value = rng2.Value
Next
Application.EnableEvents = True
End Sub

Upvotes: 1

Related Questions