Macro to replace empty cells in selection with 0

[BabsKy]

I’m trying to write a macro that finds empty cells in a selection and replaces them with 0 (zero).
I’m pretty much there, I have a macro that loops the selection and finds empty cells, the problem I have is all I can find to do the find & replace is ReplaceDescriptor, the ReplaceDescriptor.ReplaceString is, of course, a string. I can’t find much in the way of properties, methods etc. Is there a ReplaceDescriptor.ReplaceString numeric equivalent or am I trying to convert it wrong? (which I’ve given up with as it doesn’t like any conversion methods I’ve tried!)
I probably don’t need an input box but it makes the macro less restricted.
This is what I have so far;

Dim Doc As Object
Dim Sheet As Object
Dim oRange As Object 
Dim ReplaceDescriptor As Object
Dim I As Integer
Dim InputValue As Integer

 
Doc = ThisComponent
Sheet = Doc.Sheets(0)
oRange = Doc.currentselection

	InputValue = CInt(InputBox("Enter value that will fill empty cells in selection", _
	"Fill Empty Cells"))
 
ReplaceDescriptor = Sheet.createReplaceDescriptor()
ReplaceDescriptor.SearchString = ""
ReplaceDescriptor.ReplaceString = InputValue

For I = 0 to Doc.Sheets.Count - 1
   Sheet = Doc.Sheets(I)
   oRange.ReplaceAll(ReplaceDescriptor)
Next I

[Lupp]

The ThisComponent.CurrentSelection is always a range within one specific sheet. The sheetname is part of the AbsoluteName property, the sheet number is stored in the RangeAddress property. If you want to walk through the sheets processing equally addressed ranges of each one, you have to create the specific range for each sheet one by one.

F&R’ with the help of a replace descriptor may not be the optimal approach here.

The following code does not loop through the sheets but demonstrates a few aditional issues.

REM  *****  BASIC  *****

Sub setValue_0_for_Blank()
REM Completely blank cells are recognised by their type being 0 (zero)
theRange = ThisComponent.CurrentSelection
For x = 0 To theRange.RangeAddress.EndColumn - theRange.RangeAddress.StartColumn
    For y = 0 To theRange.RangeAddress.EndRow - theRange.RangeAddress.StartRow
        theCell = theRange.GetCellByPosition(x, y)
        If (theCell.Type=0) Then theCell.Value = 0
    Next y
Next x
End Sub

Sub setValue_0_for_Empty()
REM "Empty" cells may be blank or have the empty text returned by a formula as the result.
REM A cell displaying the empty text due to a format setting or a conditional format does NOT get the 0 value.  
theRange = ThisComponent.CurrentSelection
theDataArray = theRange.GetDataArray
For x = 0 To theRange.RangeAddress.EndColumn - theRange.RangeAddress.StartColumn
    For y = 0 To theRange.RangeAddress.EndRow - theRange.RangeAddress.StartRow
        theCell = theRange.GetCellByPosition(x, y)
        If ((theCell.Type=0) OR  ((theCell.FormulaResultType=2)AND(theDataArray(y)(x)=""))) Then theCell.Value = 0
    Next y
Next x
End Sub  

The second Sub will replace a formula with the numeric content 0 under certain conditions.

[BabsKy]

Thank you that works perfectly for me. I’ll probably only use the macro on one sheet at a time anyway (for now). I used the first sub and it does just what I want it to. Probably should have said I’m new to LOB, your first sub is how I would’ve done it but I was getting stuck. Thanks again for your help