Replace custom format

[robstar72]

Is it possible to use Find&Replace to change from one custom format to another custom format i a range? Or is there another way to do this?

/Robert

 

[Jon von der Heyden]

Hi Robert and welcome to MrExcel

You can find formats with Find&Replace, but you cannot replace the format. You need to either find them all, and select the find selection and change the format manually, or use VBA.

 

[christianrwevans]

Hi Jon and Robert,

This thread seems like the most related to my issue.

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 lesser-than number formatting.

My approach so far has been the 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:
[TABLE="width: 64"]
<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]General Format
<0
[/TD]
[/TR]
[TR]
[TD="class: xl63"]<0.1[/TD]
[/TR]
[TR]
[TD="class: xl63"]<0.01[/TD]
[/TR]
[TR]
[TD="class: xl63"]<0.001[/TD]
[/TR]
[TR]
[TD="class: xl63"]<0.0001[/TD]
[/TR]
[TR]
[TD="class: xl63"]<0.00001[/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="width: 64"]
<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]< 0
[/TD]
[/TR]
[TR]
[TD="class: xl67"]< 0.0[/TD]
[/TR]
[TR]
[TD="class: xl68"]< 0.00[/TD]
[/TR]
[TR]
[TD="class: xl65"]0.001[/TD]
[/TR]
[TR]
[TD="class: xl65"]0.0001[/TD]
[/TR]
[TR]
[TD="class: xl65"]0.00001[/TD]
[/TR]
</tbody>[/TABLE]

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. Might you be able to shed any light on this?

Thanks very much,
Christian

 

[christianrwevans]

(sorry, re-posting since all the code did not make it in)
Hi Jon and Robert,

This thread seems like the most related to my issue.

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 lesser-than number formatting.

My approach so far has been the 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:
[TABLE="width: 64"]
<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]General Format
< 0
[/TD]
[/TR]
[TR]
[TD="class: xl63"]< 0.1
[/TD]
[/TR]
[TR]
[TD="class: xl63"]< 0.01
[/TD]
[/TR]
[TR]
[TD="class: xl63"]< 0.001
[/TD]
[/TR]
[TR]
[TD="class: xl63"]< 0.0001
[/TD]
[/TR]
[TR]
[TD="class: xl63"]< 0.00001
[/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="width: 64"]
<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]< 0
[/TD]
[/TR]
[TR]
[TD="class: xl67"]< 0.0[/TD]
[/TR]
[TR]
[TD="class: xl68"]< 0.00[/TD]
[/TR]
[TR]
[TD="class: xl65"]0.001[/TD]
[/TR]
[TR]
[TD="class: xl65"]0.0001[/TD]
[/TR]
[TR]
[TD="class: xl65"]0.00001[/TD]
[/TR]
</tbody>[/TABLE]

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. Might you be able to shed any light on this?

Thanks very much,
Christian