Formatting Cells in Visual Basic

mtp9302

New Member
Joined
Aug 1, 2006
Messages
22
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.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,075
Office Version
  1. 365
Platform
  1. Windows
Try this.
Code:
With Range("E5")
      .Value = Val(MileageData)
      .NumberFormat = "0"
End With

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

charoo

New Member
Joined
Jan 5, 2009
Messages
24
2.5 years later, this question and answer was still helpful!

THANK Mr.Excel.com!
 

Watch MrExcel Video

Forum statistics

Threads
1,119,063
Messages
5,575,892
Members
412,689
Latest member
nhsmedic
Top