title: VBA for replacing format but preserving cell value: partially solved
body: <p>I am trying to put together VBA to search for a specific cell format and then change that cell format. I got inspiration from this post (<a href="https://stackoverflow.com/questions/25825136/excel-vba-value-remains-string-format-after-replacment/25825569#25825569">Excel VBA value remains string format after replacment</a>) and hoped to get more from this post (<a href="https://stackoverflow.com/questions/27950093/excel-vba-add-a-custom-number-format">Excel VBA - add a custom number format</a>) but couldn't quite do it.</p>
  
  <p>The cell formats I am wanting to replace are <strong>formatted general</strong> in the following 6 varieties:</p>
  
  <ol>
  <li>&lt; 0</li>
  <li>&lt; 0.1</li>
  <li>&lt; 0.01</li>
  <li>&lt; 0.001</li>
  <li>&lt; 0.0001</li>
  <li>&lt; 0.00001</li>
  </ol>
  
  <p>I want to replace them with proper number formatting ("&lt; 0", "&lt; 0.0", etc.) and the remove the text symbol "&lt;" so that they display as "&lt; 0" but are stored as a number for use in calculations.</p>
  
  <p>My approach so far has been the VBA Replace function. For a given range, I have been trying to run 6 different replacements for the 6 different formats and then remove all text symbols for "&lt;", but I am stuck on replacing the cell value with its own value: currently all cells are replaced with the same value (the value of the first cell in the range).</p>
  
  <p>The code I have developed so far is:</p>
  
  <pre><code>Private Sub CommandButton6_Click()
  
  Dim range1 As Range
  
  With ActiveWorkbook.Worksheets("Sheet1")
      Set range1 = .[B2:B7]
  End With
  
  For Each cell In range1.Cells
      Dim Original0value As String
      Original0value = cell.Value
      Application.findformat.NumberFormat = "General"
      Application.ReplaceFormat.NumberFormat = "&lt; 0"
      With range1
          .Replace What:="&lt; ?", Replacement:=Original0value, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchFormat:=True, ReplaceFormat:=True
      End With
  Next cell
  
  For Each cell In range1.Cells
      Dim Original1value As String
      Original1value = cell.Value
      Application.findformat.NumberFormat = "General"
      Application.ReplaceFormat.NumberFormat = "&lt; 0.0"
      With range1
          .Replace What:="&lt; ???", Replacement:=Original1value, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchFormat:=True, ReplaceFormat:=True
      End With
  Next cell
  
  For Each cell In range1.Cells
      Dim Original2value As String
      Original2value = cell.Value
      Application.findformat.NumberFormat = "General"
      Application.ReplaceFormat.NumberFormat = "&lt; 0.00"
      With range1
          .Replace What:="&lt; ????", Replacement:=Original2value, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchFormat:=True, ReplaceFormat:=True
      End With
  Next cell
  
  ' et cetera - I have only include the first 3 blocks of a possible 6
  
      Cells.Replace What:="&lt;", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
  
  End Sub
  </code></pre>
  
  <p>This gives me the result:</p>
  
  <ol>
  <li>&lt; 0</li>
  <li>&lt; 0.0</li>
  <li>&lt; 0.00</li>
  <li>0.001</li>
  <li>0.0001</li>
  <li>0.00001</li>
  </ol>
  
  <p>I.e., the first 3 items are formatted correctly but the values have all been changed to 0. As I am using "cell.Value" in each block I suspect this is the source of my problem but can't quite figure it out. Can anyone shed any light on this?</p>