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!
excel-find and convert/format dates and dollar values
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)
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.
Here are samples of what I do now:
Dollar columns are always 13.5 wide and dates 10.5 users don’t like autofit as they want columns to look uniform
Thanks !
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)
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.
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"
Dollar columns are always 13.5 wide and dates 10.5 users don’t like autofit as they want columns to look uniform
Thanks !
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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?
In the file uploaded I do not see any $s. Can you upload something which contains $s as well?
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.
Thanks.
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.
Thanks.
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?
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
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
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
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
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
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
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial