VBA Replace format but not value

[christianrwevans]

Hi gurus,

I am trying to put together VBA to search for a specific cell format and then change that cell format. The cell formats I am wanting to replace are the variety between "< 1" and "< 0.00001" (formatted general); I want to replace them with proper number formatting ("< 0", etc.).

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 "<", 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).

Specifically, the test column I am working with is:
General Format
< 0
< 0.1
< 0.01
< 0.001
< 0.0001
< 0.00001

And the codes I am using so far is:

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 = "< 0"
    With range1
         .Replace What:="< ?", 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 = "< 0.0"
    With range1
         .Replace What:="< ???", 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 = "< 0.00"
    With range1
         .Replace What:="< ????", 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:="<", Replacement:="", LookAt:=xlPart,  SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,  ReplaceFormat:=False

End Sub

This gives me the result:
< 0
< 0.0
< 0.00
0.001
0.0001
0.00001

Where 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?

Thanks very much,
Christian

 

[christianrwevans]

Update to this thread: someone pointed out that "<0" formatted general should be treated as text. Thus instead of:

.Replace What:="< ???", Replacement:=Original0value, LookAt:=xlWhole,  SearchOrder:=xlByRows, SearchFormat:=True, ReplaceFormat:=True

I have used:

.Replace What:="<?", Replacement:=Mid(Original0value, 2, 1), LookAt:=xlWhole, SearchOrder:=xlByRows, SearchFormat:=True, ReplaceFormat:=True

This has solved that particular issue but upon scaling this up, I have run into another problem: with multiple instances of cells which needs re-formatting, the VBA works just as intended for custom formats "< 0.0" through "< 0.00000", but for some reason it copies the same value for the first instance of "<0" it encounters into all other instances of this formatting.

It's a little painful to describe so please check out my example spreadsheet on Dropbox: the sheet has test data, the VBA, and a description of the issue.

Thanks!

Cheers, Christian

 

[christianrwevans]

I have been made aware of the value of posting links to my cross-posts in other forums:

For clarity:
I have posted the original version of this problem (can't get VBA to preserve cell value during changing of cell formatting from general to numerical while removing "<" symbol) in these places
replacing formatting with VBA
http://www.mrexcel.com/forum/excel-...pplications-replace-format-but-not-value.html
http://www.mrexcel.com/forum/excel-questions/727735-replace-custom-format.html (question appended to end of someone else's post)
excel - VBA for replacing format but preserving cell value - Stack Overflow

A first stage of the problem was solved (VBA will now work as intended with a minimal dataset) at the StackExchange link above. I have posted a new question for the next stage of problem-solving this VBA at:
excel - VBA for changing multiple cell formats - stop values from repeating - Stack Overflow

Thanks,
Christian