(VBA EXCEL) Replace only 0 with four spaces

Question

This is an oddly specific question. Suppose I had a cell that yields a value of 0. I am writing it to a text file.

When I write it instead of 0 I want there to be four spaces (it is essentially a visual placeholder for null).

Problem with this line:

cellValue = Replace(cellValue, 0, "    ")

This tends to replace ALL zeroes with four spaces. Does anybody have a clue as to how to solve this? I would appreciate any and all help.

Sample input:

0 | 100 | 48

Sample Output:

____10048 (Underscores are spaces).

What I have so far:

Dim myFile As String, rng As Range, cellValue As Variant, I As Integer, j As Integer

myFile = Application.DefaultFilePath & "\test_data_output.txt"
Set rng = Selection
Open myFile For Output As #1                                    

For I = 1 To rng.Rows.Count
    For j = 1 To rng.Columns.Count
cellValue = Replace(cellValue, 0, "    ")
cellValue = cellValue + CStr(rng.Cells(I, j).Value)
If j = rng.Columns.Count Then
    Print #1, cellValue
End If

    Next j
    cellValue = ""
Next I

Close #1

---------------------------------UPDATE--------------------------------------

So we just decided to do a excel sheet if isBLANK condition to show 4 spaces if its false. That should work for now. Thanks for all the help. It was essentially a blank set of cells that was carried on to a new sheet. Thus for some reason it always showed 0 to show that its blank. We decided the if else would be best.

Excel Code:

=IF(ISBLANK('OurSheetName'!I7),"    ",'OurSheetName'!I8)

Answer 1

A couple of issues with your code:-

1. You have defined cellValue as a Variant for no apparent reason. You are always better to declare variables using a datatype that best represents the data. You could simply declare it as a String unless you have a very good reason to keep it as an expensive-to-use Variant.
2. I can't see where you are assigning a value to cellValue. Is this the complete code or have you edited it for posting? It is difficult to assess code if it has been hacked for posting without proper comments to explain what was removed.

You could simply test the value before writing it:-

...

'Inside the "For j" loop

cellValue = rng.Value

'Test the value - but test it as a string value
If cellValue = "0" Then
  cellValue = "    "     'Replace with 4 spaces
End If

'Carry on with code...

Answer 2

Before making the replacement, just check if the cell value is 0.

For I = 1 To rng.Rows.Count
  For j = 1 To rng.Columns.Count
    If cellValue = 0 Then
       cellValue = Replace(cellValue, 0, "    ")
    End If
    If j = rng.Columns.Count Then
       Print #1, cellValue
    End If
  Next j
  cellValue = ""
Next I