Need help with formatting numbers in cells that have text using VBA

[seattletimebandit]

I have a spreadsheet table that has alphanumeric text values. Some of the numbers need to be formatted to be consistent with data in other cells (example of correct format: "0.500" and "1.10", incorrect format: "0.5" and "1.1", need to add trailing zeros).

I have a macro that can check formatting of actual numbers based on value of adjacent cell, but I'm having trouble with text values and modifying the number format.

The code from another table format (note that numbers with trailing zeros after decimal point differ based on the value of the number. The table I pasted below shows the inconsistencies of Columns 3, 5, 7, and 9).

As I stated above, note that the alpha characters in my new table are in the same cell contrary to my code below that was looking for letter values in adjacent cells.

Sub FormatDataChecking()
    
    For rw = 1 To 2000
        
'This will format the columns used for checking data against the hardcopy data
'Format and add a "<" sign to any numbers in Range D that has a "U" flag in Range E
'NOTE: DO NOt RUN THIS CODE UNTIL AFTER YOU HAVE TRANSPOSED DATA INTO FINAL TABLE COLUMNS
'IF THE DATA IS FORMATTED IN THE DATA CHECKING TABLE, IT WILL PREVENT THE Sub 'FormatTableColumns CODE TO NOT WORK PROPERLY
        
        If Range("D" & rw) < 1 And Range("E" & rw) = "U" Then _
            Range("D" & rw).NumberFormat = """< ""#,##0.00 "
        
        If Range("D" & rw) >= 10 And Range("E" & rw) = "U" Then _
            Range("D" & rw).NumberFormat = """< ""#,### "
        
        If Range("D" & rw) >= 1 And Range("D" & rw) <= 9.9 And _
                Range("E" & rw) = "U" Then _
                Range("D" & rw).NumberFormat = """< ""#,#0.0 "
          
'Format numbers in Range D that has an empty cell "" or a "D" data qualifier in Range E
                
        If Range("D" & rw) < 1 And Range("E" & rw) = "" Then _
            Range("D" & rw).NumberFormat = "#,##0.00 "
        
        If Range("D" & rw) >= 1 And Range("D" & rw) <= 9.9 And _
            Range("I" & rw) = "" Then Range("D" & rw).NumberFormat = "#,#0.0 "
        
        If Range("D" & rw) >= 1 And Range("D" & rw) <= 9.9 And Range("E" & rw) = "D" Then _
            Range("D" & rw).NumberFormat = "#,###0.0 "
        
        If Range("D" & rw) >= 10 And Range("E" & rw) = "H" Then _
            Range("D" & rw).NumberFormat = "#,### "
            
        If Range("D" & rw) >= 10 And Range("E" & rw) = "" Then _
            Range("D" & rw).NumberFormat = "#,### "
    
    Next
  
End Sub

		MW-01D	MW-01D	MW-02D	MW-02D	MW-03D	MW-03D	MW-04D	MW-04D
CONSTITUENT	CleanupLevel	4/11/2011	10/18/2011	4/11/2011	10/19/2011	4/13/2011	10/17/2011	4/14/2011	10/17/2011
Dichlorodifluoromethane	100	1 U	1.00 U	1 U	1.00 U	1 U	1.00 U	1 U	1.00 U
Chloromethane		0.5 U	0.500 U	0.5 U	0.500 U	0.5 U	0.500 U	0.5 U	0.500 U
Vinyl chloride	100	0.2 U	0.2 U	3.23	1.12	4.62	3.59	0.67	1.1
Bromomethane		0.5 U	0.5 U		0.500 U		0.500 U	0.500 U	0.500 U
Trichlorofluoromethane		0.5 U	0.5 U		0.500 U		0.500 U	0.500 U	0.500 U
Chloroethane		0.91	0.5 U	0.5 U	0.500 U	0.52	0.500 U	0.500 U	0.500 U
1,1-Dichloroethene	1000	17.2	0.98	12.3	4.24	16.8	12.3	0.86	3.46
Acetone		2 U	2 U	2 U	2.00 U	2.00 U	2.00 U	2.00 U	2.00 U
Methylene chloride	25	3.68	0.86	2.84	0.64	2.49	1.84	0.500 U	0.6
trans-1,2-Dichloroethene		184	64.9	124	61.3	125	111	61.7	60.2
1,1-Dichloroethane		275	7.08	164	55.3	263	174	5.87	50.8
2,2-Dichloropropane		1 U	1 U	1 U	1.00 U	1.00 U	1.00 U	1.00 U	1.00 U
cis-1,2-Dichloroethene	1100	2700	109	2200	862	2480	1860	90.8	703
Chloroform		1 U	1 U	1 U	1.00 U	1.00 U	1.00 U	1.00 U	1.00 U

<tbody>
</tbody>

 

[JoeMo]

This is a duplicate post. I have replied to your other post here: http://www.mrexcel.com/forum/excel-questions/699326-marco-tha-changes-number-format-text-values.html

 

[seattletimebandit]

JoeMo,

I thought the first post didn't go through! my browser was hanging for quite awhile, so I closed it. I couldn't find it posted, so I did it again. Weird, but thanks for letting me know.

stb