excel-find and convert/format dates and dollar values

matttclark
matttclark used Ask the Experts™
on
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 !
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Can you also upload a file which has some of the sought numbers?

Author

Commented:
Here is one sample that has a a couple different date formats.
Budget-report--With-Contract.xls
Are you referring to column X for the date format?

In the file uploaded I do not see any $s. Can you upload something which contains $s as well?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi,

This file is unconverted data so there are no $$ yet.

Columns X, Y and Z are date columns and other columns except A-D are $ columns.  

The date columns come in differently formatted, in this particular file Column X is "general" (and I also realized that this is a third format added to the 2 above.  This is mm/yyyyy as text)

The columns i convert to 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)"

Thanks.

Author

Commented:
Just to be clear--the sample code in my original post was from the code for a different file, so the columns referenced are different that the ones in the sample file.  

I need to create a macro for the sample file and others which got me to thinking if there was a way to search on the values, formats or ther criteria in the cell contents to determine how to format the cell or column, rather than manualy plug them in.

In thinking more, columns are alwasy the same, by that i mean if it has a date in a certain format in row '1' all values in that colum down to the end will have the same data type and require the same format.

Don't know if that helps, possibly since this will reuqire some sort of looping maybe you can test a cell in each column for a single row or maybe the last two-three rows (to account for blanks) and assign the whole column based on that???  Don't know if this would cut down on looping or make it easier?
Try this macro. It checks for each cell below row 1 and assigns a format. It does not address entire columns.

If you want to address entire columns then I foresee a problem. There are a number of cells in the date column which are not really dates. If the column formatting is based on the type of entry in the first row then if the first entry in the column is not a proper date it may skip the entire column.

Any lets see your comments on this.

Saqib
Sub fmtfrmvals()
Dim cel As Range
For Each cel In Range("A2", Cells(Range("A2").End(xlDown).Row, Range("A2").End(xlToRight).Column))

If IsDate(cel) Then
    prts = Split(cel, "/")
    If UBound(prts) = 1 Then
        cel = CVDate(cel)
        cel.NumberFormat = "mm/yy;@"
    Else
        cel = CVDate(cel)
        cel.NumberFormat = "mm/dd/yy;@"
    End If
ElseIf Application.IsNumber(cel) Then
    cel.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
Else
End If
Next cel
End Sub

Open in new window

Author

Commented:
Saqib,

That's beautiful, yet simple ( I guess they go hand in hand)! Accepting as the solution wish I could give more points.  

If you have time to clarify one thing for me it would help me learn. I recieve an undefined variable error for 'prts' if the module is set to option explict.  How should that be defined?  I tried Integer, Long and Object based on what I think you are doing in the code, but then I receive an error 'Expected Array' at this line:

If UBound(prts) = 1 Then

Thanks if you have time to address, it's cool if you don't.  Thanks again for the solution.

Matt
Try string

Author

Commented:
same error
Dim prts() As String

works fine. I just tried it.

Actually I have this bad habit of working without OE and dim's so could not respond without trying it.

Saqib

Author

Commented:
yeah.....  Dim prts() As String worked:    Dim prts As String as I originally typed didn't (doh!)......

thanks again Saqib, this will save me tons of time.

Follow up note to anyone else using this.  I ran this on about 10 reports and worked like a charm except on 1 report.  I didn't not realize previously that I could have blanks in my data (the sample report I gave to Saqib didn't).  If this is the case for you, then the range selection used above may fail to cover all rows/columns if you have a blank in column A or row 2.  In my case I replaced it with:

For Each cel In ActiveSheet.UsedRange  ' UsedRange on new exports

Reading on VBA out there, UsedRange is fraught with perils, however since my reports are 'clean' exports, it should work for my case.  If you use Saqib' s code, please be sure to research the different options to select a used range that would apply to your situation.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial