Convert dashes to zero - VBA

[NinaE_11]

Hello,
I have a contiguous range of data that I plan to do apply a number of different functions to, however, due to source of material, some of the data points will use a "--" in lieu of zero. I would like to see if there is any VBA language that can convert these dashes to use a "0.00" format instead.

So far, I have tried the following:
Range("B1").currentregion.select
selection.numberformat = "0.00"

However, this does not update the dashes to the 0.00 which allows the functions to work properly. Any help would be much appreciated - thank you!

 

[NinaE_11]

If you use
=Code(C4)
looking at one of those cells, what does it return?

It came back with a '45'. Is that meaningful? I am clueless.

 

[NinaE_11]

Maybe I should just try to clear out the "--" altogether. Would that be easier? So it's a blank space - would have the same affect to formula.

 

[Joe4]

My data starts in cell C7, but I replaced the "B1" he provided with the C7 reference.

OK, if you put "C7" instead of "B1" in the MsgBox line of code I sent you, what exactly does the MsgBox return?

 

[Fluff]

It came back with a '45'. Is that meaningful? I am clueless.

That's what I would expect to see, it's the code number for a -
Does this work

Sub NinaE()
   With ActiveSheet.UsedRange
      .Replace "--", 0, xlPart, , , , False, False
      .Value = .Value
   End With
End Sub

 

[NinaE_11]

My data starts in cell C7, but I replaced the "B1" he provided with the C7 reference.

MsgBox says:

$A$1:$H$5

 

[NinaE_11]

That's what I would expect to see, it's the code number for a -
Does this work

Sub NinaE()
   With ActiveSheet.UsedRange
      .Replace "--", 0, xlPart, , , , False, False
      .Value = .Value
   End With
End Sub

Thank you - this worked! It didn't change the visual to a 0, but it calculates the formula as it should and I don't get an error anymore! Thank you so much.

 

[Fluff]

Glad it's sorted & thanks for the feedback.

 

[Joe4]

$A$1:$H$5

That tells you what your problem was. Your Current Region statement was only selecting A1:H5 in your data (meaning that row 6 is blank and column I is blank). Current Region only selects the contiguous cells, so if you do not pick a cell that is within your data range to start, or if you have completely blanks rows and/or columns in the middle of the data, Current Region will not return all your data.

Fluff's solution looks at the entire usedd range.