Formatting Cells in Visual Basic

[mtp9302]

Hi, I have an application where I am using a UserForm to have the spreadsheet user type in several pieces of data. I have successfully used this and the data has been translated onto the spreadsheet as desired, except for the formatting. The data that is being translated consists of two strings, a date, a number, and a currency value. The strings and date come out fine. The number and currency value come up with those little green earmark errors, indicating that the cell contain numbers stored as text. I put the following lines of code in the UserForm code to fix this, but they didn't do anything (the mileage in E5 is the number and the cost in F5 is the currency):

Range("E5").Value = MileageData
Selection.NumberFormat = "0"
Range("F5").Value = CostData
Selection.NumberFormat = "$#,##0.00"

I think I need to convert the text to a number, like the green earmark error suggests, but I don't know how to code this. I tried to record a macro to do it, but it just gave me the commands I have listed above. If anyone has any ideas on how to accomplish this, I'd be most appreciative for any information. Thanks.

 

[Norie]

Try this.

With Range("E5")
      .Value = Val(MileageData)
      .NumberFormat = "0"
End With

With Range("F5")
       .Value = Val(CostData)
       .NumberFormat = "$#,##0.00"
End With

 

[mtp9302]

Perfect. Thanks much for your help, I appreciate it.

 

[charoo]

2.5 years later, this question and answer was still helpful!

THANK Mr.Excel.com!

 

[charoo]

bump?