replacing formatting with VBA

C

christian.rw.evans

New member
Joined
Jul 7, 2016
Messages
6
Reaction score
0
Points
0
Excel Version(s)
Office 365
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:
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 = "< 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
 
Hey @christian.rw.evans -- I'm not sure I understand your end goal.

It seems like you want the cell formatting to be changed from "General" to "Number" in each of the target cells, but can you clarify on the decimals that you want to show?

It would be super helpful if you shared a workbook containing (1) the original data that you start with and (2) the data that you want to end up with.

Thanks!
Dan
 
Hi Dan,

Many thanks for your reply, I appreciate your time and expertise. I have attached a sheet with the original data formats, the desired data formats, and the VBA I've come up with so far. This is just test data but I am looking for format changes for 6 different formats:
1. <1
2. <0.1
3. <0.01
4. <0.001
5. <0.0001
6. <0.00001

This is for analytical data which can be any of those 6 degrees of precision and which will be found throughout the sheet (actually, just throughout the column since we will be adding new unformatted data to older, formatted data). The tricky part is that I want to maintain the same value and same number of digits in the re-formatted cell, just with the "<" removed and included as part of a custom number format instead.

Hopefully the attachment is enough for you to understand what I'm trying to get at.

Thanks again,
C
 

Attachments

  • Sample Data.xlsm
    29.2 KB · Views: 20
Last edited:
Update to this thread: someone pointed out that "<0" formatted general should be treated as text. Thus instead of:

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

I have used:
Code: 
.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
 

Attachments

  • Formatting VBA.xlsm
    26.7 KB · Views: 29
Posting troubles.

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 : the sheet has test data, the VBA, and a description of the issue.
 
cross posted without links:
http://www.mrexcel.com/forum/excel-...pplications-replace-format-but-not-value.html
http://stackoverflow.com/questions/38251184/vba-for-replacing-format-but-preserving-cell-value
http://www.mrexcel.com/forum/excel-questions/727735-replace-custom-format.html (actually a hijacked thread)

christian.rw.evans, for your information, you should always provide links to your cross posts.
This is a requirement, not just a request.
If you have cross posted at other places, please add links to them too.
Why? Have a read of http://www.excelguru.ca/content.php?184
 
Hi p45cal,
Thanks for the tip: I wasn't aware of the context for the cross-posting issue.

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
excelguru.ca/forums/showthread.php?6335-replacing-formatting-with-VBA (this page)
mrexcel.com/forum/excel-questions/951574-visual-basic-applications-replace-format-but-not-value.html
mrexcel.com/forum/excel-questions/727735-replace-custom-format.html (question appended to end of someone else's post)
stackoverflow.com/questions/38251184/vba-for-replacing-format-but-preserving-cell-value

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:
stackoverflow.com/questions/38798779/vba-for-changing-multiple-cell-formats-stop-values-from-repeating

(Can't yet post links with my status...)

Thanks,
Christian
 
(re-posting since now I can post links)
Hi p45cal,
Thanks for the tip: I wasn't aware of the context for the cross-posting issue.

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
http://www.excelguru.ca/forums/showthread.php?6335-replacing-formatting-with-VBA (this page)
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)
http://stackoverflow.com/questions/38251184/vba-for-replacing-format-but-preserving-cell-value

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:
http://stackoverflow.com/questions/...tiple-cell-formats-stop-values-from-repeating

Thanks,
Christian
 
You must log in or register to reply here.
Back
Top