title: Excel VBA - add a custom number format
body: <p>I have a file, generated outside Excel with many percentages. All these percentages have a single decimal place. When imported into excel, Excel adds a second decimal place to the percentages - this seems to be some default format for percentages in Excel. It just adds a "0".</p>
  
  <p>I want to format all double-decimal places percentages to a single decimal point. If I do this manually, by using CTRL+H, Find format/replace format, it goes well. But I cannot do this via VBA/macro, even if I record the macro while doing the replacement manually. The code generated by recording the macro is this:</p>
  
  <pre><code>Application.FindFormat.NumberFormat = "0.00%"
  Application.ReplaceFormat.NumberFormat = "0.0%"
  Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
      xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
  </code></pre>
  
  <p>The failure happens at:</p>
  
  <pre><code>Application.ReplaceFormat.NumberFormat = "0.0%"
  </code></pre>
  
  <p>After a lot of trial and error, I found that the new format needs to be in the Custom format list. But single-decimal place percentages is not there by default. If I manually format a single cell to the desired format, the macro works. Of course, I do not want to do that with every file.</p>
  
  <p>Any input would be much appreciated.</p>
  
  <p>Thanks in advance!</p>