troubleshooting Question

excel-find and convert/format dates and dollar values

Avatar of matttclark
matttclarkFlag for United States of America asked on
Microsoft ExcelVisual Basic Classic
12 Comments1 Solution502 ViewsLast Modified:
Is there a way in VBA to find Date and Dollar values on imported data and set the appropriate formatting?

Using Excel 2003 with data from Access using either 1) Query -Tools, Office Links, Analyze with Excel or 2) the DoCmd.OutputTo acQuery… MicrosoftExcelBiff8(*.xls) from within Access

I am in the process of creating report specific macros’ for our teams users that do a lot of formatting on each query and turn it into a report, but am wondering if there is a better way. Now I create a macro to manually select and format the columns that have date values and the same for dollar values. I have to go through each report and code these columns in, and also go back and update with changes.  Users can run these from an add-in any time they want against the imported data.

Is there a way to go through a workbook in code and find cells with dates and convert them if needed and set the date format?  Most come up as dates but some as text. The two formats I start with are either d/mm/yy (as date or text) and dd-mmm-yy (as date or text).  These I change to dd/mm/yyyy.  

So far dollar values always seem to be as numbers in  “#,##0.00;(#,##0.00)” format. and I just change the format of these to "$#,##0.00_);[Red]($#,##0.00)"

Here are samples of what I do now:

 Columns("E:AD").Select
        Selection.ColumnWidth = 13.5
        Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)

Columns("W:Z").Select
        Selection.NumberFormat = "mm/dd/yy;@"
        Selection.HorizontalAlignment = xlCenter
        Selection.ColumnWidth = 10.5"

Open in new window


Dollar columns are always 13.5 wide and dates 10.5 users don’t like autofit as they want columns to look uniform

Thanks !
ASKER CERTIFIED SOLUTION
Saqib Husain
Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros