How to do a find and replace on truly blank cells

Question

I have a range, that can be variable in size, and can include several tens of thousands of cells. For every cell in this range that has a string in it, I need to replace with a 1. For every cell with no value at all, I need to replace with a zero.

I tried the following, but while it did replace the filled in cells with ones, the blank cells remained blank.

Selection.Replace What:="*", Replacement:="1", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

I also tried this with the same results.

Selection.Replace What:=null, Replacement:="0", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

EDIT: to include the full code

Sub MassFindReplace()

        ' This will select an area within the given parameters and replace all blank cells with zeros and all populated cells with Ones

    Dim VRange1 As String
    Dim VRange2 As String
    Dim Doublecheck As Integer

    VRange1 = InputBox("Enter First Cell Address Here" & vbNewLine & vbNewLine & "Make sure you ONLY input a single cell address")

    VRange2 = InputBox("Enter Second Cell Address Here" & vbNewLine & vbNewLine & "Make sure you ONLY input a single cell address")

    Range(VRange1, VRange2).Select

    Doublecheck = MsgBox("The range you have selected is between " & VRange1 & " and " & VRange2 & vbNewLine & vbNewLine & "Does this sound right to you?" & vbNewLine & vbNewLine & "If not press No to cancel", vbYesNo)

    If Doublecheck = vbYes Then

    ' This turns off a number of background functions and greatly speeds up this process
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' choose what to search for and what to replace with here
    Selection.Replace What:="*", Replacement:="1", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Cells.SpecialCells(xlCellTypeBlanks).Value = 1


    'Resets the background functions. THIS MUST HAPPEN or it will screw up your excel.
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.CalculateFull

    MsgBox "Complete"

    Else
        MsgBox "Canceled"

    End If

End Sub

EDIT: I tried basing this after some of the code below, but while it seems to work, I can't get it to select a custom range.

Sub MassTEST()



Dim ws As Worksheet: Set ws = ActiveSheet
Dim cel As Range
Dim VRange1 As String
Dim VRange2 As String
Dim Doublecheck As Integer


VRange1 = InputBox("Enter First Cell Address Here" & vbNewLine & vbNewLine & "Make sure you ONLY input a single cell address")

VRange2 = InputBox("Enter Second Cell Address Here" & vbNewLine & vbNewLine & "Make sure you ONLY input a single cell address")

Data = ws.Range(VRange1, VRange2).Value

For Each cel In ws.UsedRange
    If cel.Value <> "" Then
        cel.Value = 1
    Else
        cel.Value = 0
    End If
Next

End Sub

Answer 1

Use "~*".

Selection.Replace What:="~*", Replacement:="1", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Answer 2

If you have to go through and evaluate EVERY cell, then just check each cell to see if it's empty. Granted, if the worksheet's UsedRange is not the range you need, you can specify it manually.

Sub MassFindReplace()

    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim cel As Range

    For Each cel In ws.UsedRange
        If cel.Value <> "" Then
            cel.Value = 1
        Else
            cel.Value = 0
        End If
    Next

End Sub

Per urdearboy's suggestion, you can also load it into an array and then check there.

Sub MassFindReplace()

    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim data As Variant, v As Variant

    data = ws.UsedRange.Value

    For i = LBound(data, 1) To UBound(data, 1)
        For j = LBound(data, 2) To UBound(data, 2)
            If data(i, j) <> "" Then
                 data(i, j) = 1
            Else
                data(i, j) = 0
            End If
        Next
    Next

    ws.UsedRange.Resize(UBound(data, 1), UBound(data, 2)).Value = data

End Sub

Answer 3

use this:

On Error Resume Next
    Selection.Cells.SpecialCells(xlCellTypeBlanks).Value = 1
On Error GoTo 0

Note it will only fill the intersection of the UsedRange and the Selected Cells.