IF statement that will return a value base on the contents of a cell

[JAeEM]

I'm trying to write an If statement that will keep the current value in the cell if there is a value. If the cell is blank I would like to write "Not Available". Please help. Thank you all so much!

 

[Logit]

Let's presume you have data entered in Cell A1 down to Cell A20. However,
some of these rows in Col A are empty.

In B1 enter this formula : =IF(A1="","Not Available","")
Then drag that formula down Col B.

If you are not wanting a formula but rather a VBA macro, that can be done also.

 

[Logit]

Here is a macro method :

Sub IsItEmpty()
  Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Application.ScreenUpdating = False
    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("Sheet1")
       
    Application.ScreenUpdating = False
   
    For Each c In Source.Range("A1:A200")  ' change range as needed
        If c.Value = "" Then
          c.Value = "Not Available"
        End If
    Next c
   
    Source.Range("A1").Select
    Application.ScreenUpdating = True
End Sub

 

[JAeEM]

You guys are the bomb!!! Thank you this is what I needed

 

[Logit]

You are welcome.

 

[Peter Bartholomew]

There is a 'cunning plan' that works with Logit's formula solution. Namely, if you reverse columns A and B so that the data is in column B and the formula is in column A, the text "Not Available" produced by the formula in column A will overflow into the blank cell of column B. By reducing column A to 8pts and indenting the alignment by 1, the text will appear to be in the data column B.

Of course, if you reference the data, you have to remember that the cell in column B is blank, otherwise you could finish up looking somewhat foolish!

 

[Logit]