Convert dashes to zero - VBA

NinaE_11

New Member
Joined
Aug 18, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

NinaE_11

New Member
Joined
Aug 18, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,547
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
Sub NinaE()
   With ActiveSheet.UsedRange
      .Replace "--", 0, xlPart, , , , False, False
      .Value = .Value
   End With
End Sub
 

NinaE_11

New Member
Joined
Aug 18, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Aug 18, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
That's what I would expect to see, it's the code number for a -
Does this work
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,547
Office Version
  1. 365
Platform
  1. Windows
Glad it's sorted & thanks for the feedback.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
$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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,523
Messages
5,636,820
Members
416,943
Latest member
kitkat22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top