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

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

 

[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