VBA Code - Enter specific text if cell is blank

[mickyflash]

Hi I would like to have a VBA code that enters the word "search" into B20 if B20 is blank.


I do not want this to be a formula as I will require the user to enter text into B20


I know very little about VBA and have found the following codes that seem to do similar but I cant seem to adapt them, here is what I have below:

Option Explicit 
Sub RangeSetEmpty() 
    Replace_Blanks [b20], "Search"
End Sub 
 
Sub Replace_Blanks(rangeToEmpty As Range, replacementText As String) 
    Dim cell As Range 
    For Each cell In rangeToEmpty 
        If cell.Value = "" Then cell.Value = replacementText 
         'Application.Goto Reference:=rangeToEmpty
    Next cell 
End Sub

or

Sub Replace_Blanks() 
     
    Application.Goto Reference:="B20" 
    Selection.Replace What:="", Replacement:="Search",
LookAt:=xlPart, _ 
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
    ReplaceFormat:=False 
     
End Sub

Can anyone see what I am doing wrong?

 

[BrianMH]

Sub B20Blank()
If Range("B20") = "" Then Range("B20") = "Blank"
End Sub

 

[mickyflash]

wow that was simpler than I imagined.

Thanks so much

 

[BrianMH]

Glad to help!

 

[Cloverken]

Instead of placing this macro in a module as you normally would while you are in Visual Basic if you click the worksheet that this macro pertains to and select the right side pane and place this automatic macro.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B20").Value = "" Then
Range("B20").Value = "search"
End If
End Sub

 

[mickyflash]

Thanks Cloverken, that works too!

 

[Russk68]

How would I apply this to:
A5,A9,A13 and so on?
Thanks!

 

[TotallyConfused]

Hello Russk68

I’m certainly no expert in the use of Excel, but you might use a modified version of BrianMH’s suggestion from post #2 . You can delete the word “Blank” and replace it with whatever text you want to appear there.

Sub BlankOutCells()
    If Range("A5") = "" Then Range("A5") = "Blank"
    If Range("A9") = "" Then Range("A9") = "Blank"
    If Range("A13") = "" Then Range("A13") = "Blank"
End Sub

I hope this helps.
TotallyConfused

 

[rwe1187]

What if I wanted to check the values in two cells. Say I wanted to say if cell b4 and f4 is blank then leave blank but of cell b4 has text and f4 is blank then place an X in cell f4... how would I adapt that?

 

[Joe4]

What if I wanted to check the values in two cells. Say I wanted to say if cell b4 and f4 is blank then leave blank but of cell b4 has text and f4 is blank then place an X in cell f4... how would I adapt that?

It is usually better to start your own thread than it is to respond to a thread which is 8 years old!
By starting your own thread, it shows up as a new unanswered question on the "Unanswered threads" list, which stands a much better chance of being seen and answered.

That being said, I think you want something like this:

Sub MyCheck()
    If Range("B4") <> "" And Range("F4") = "" Then Range("F4") = "X"
End Sub