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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,931
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,114,406
Messages
5,547,759
Members
410,811
Latest member
adustin42
Top