Convert dashes to zero - VBA

NinaE_11

Board Regular
Joined
Aug 18, 2020
Messages
54
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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
How about
VBA Code:
Sub NinaE()
   Range("B1").CurrentRegion.Replace "--", 0, xlWhole, , , , False, False
End Sub
or just use Ctrl H on the sheet & replace -- with 0
 
Upvote 0
Unfortunately, I cannot get this to work. My preference would be to find VBA language.

Here's a sample bit of the data:
681116109Ollie's Bargain Outlet Holdings Inc16.38682.99382.99383.0952
68213N109Omnicell, Inc.9.09521.06881.06881.1050
69753M105Palomar Holdings, Inc.36.78430.63020.63020.6515
70438V106Paylocity Holding Corp.26.48471.97651.97652.0434
74758T303Qualys, Inc.17.88592.27352.27352.3505
75606N109RealPage, Inc.21.16223.20483.20483.3133
76029L100Repay Holdings Corp. Class A--3.2753----
759916109Repligen Corporation37.25691.10281.10281.1401
825698103Shyft Group, Inc.9.25782.44722.44722.5301
83417Q105SolarWinds Corp.24.53302.27562.27562.3526
78463M107SPS Commerce, Inc.12.98123.02483.02483.1273
873379101Tabula Rasa Healthcare, Inc.45.35451.57261.57261.6259
87874R100TechTarget, Inc.8.28401.63751.63751.6929
89531P105Trex Company, Inc.16.34411.54381.54381.5961
91544A109Upland Software, Inc.44.75741.77311.77311.8332
 
Upvote 0
In what way doesn't it work?
 
Upvote 0
Are the values you are trying to replace hard-coded values, or are they formulas?

If they are formulas, you probably just need to change the Number Format applied to those columns.

If they are hard-coded values, are there extra spaces before or after the "--"?
An easy way to check is to use the LEN function, i.e. pick a cell returning this (let's say C4), and enter this formula in any blank cell:
Excel Formula:
=LEN(C4)
If it is anything other than 2, you have extra characters that you need to account for in the replace code.
 
Upvote 0
Are the values you are trying to replace hard-coded values, or are they formulas?

If they are formulas, you probably just need to change the Number Format applied to those columns.

If they are hard-coded values, are there extra spaces before or after the "--"?
An easy way to check is to use the LEN function, i.e. pick a cell returning this (let's say C4), and enter this formula in any blank cell:
Excel Formula:
=LEN(C4)
If it is anything other than 2, you have extra characters that you need to account for in the replace code.


They are hard coded values, and I did the =LEN test and it comes back with a 2. So, I'm not sure what I'm doing wrong. I'm sure I'm doing something incorrectly, but I copied the code above directly from the answer and just changed the range cell. ??
 
Upvote 0
Does your data really begin on row 1?
Are there any completely blank rows or columns in the middle of your data?

What does this line of code return?
VBA Code:
MsgBox Range("B1").CurrentRegion.Address
 
Upvote 0
If you use
=Code(C4)
looking at one of those cells, what does it return?
 
Upvote 0
Does your data really begin on row 1?
Are there any completely blank rows or columns in the middle of your data?

What does this line of code return?
VBA Code:
MsgBox Range("B1").CurrentRegion.Address

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

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

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
Back
Top