Go Premium for a chance to win a PS4. Enter to Win


Excel VBA Macro to prepare data for export

Posted on 2013-01-21
Medium Priority
Last Modified: 2013-01-28
I have only basic VBA knowledge and looking for help with VBA code to automate a process I do every week manually. The attached workbook is from Crystal Reports and has non-printing characters or leading/trailing spaces in the date column (Column A) which are not properly formatted as a short date. I currently use the Easy-XL add in to remove spaces before and after on all cells which solves that problem so the column can be properly formatted. However, I want this as part of the macro instead of having to do this step manually. The date from A2 must also be entered in the last row of Column A which would be A51 once the next step of inserting needed rows is accomplished. The report is always for one week and once processed will always have 51 rows of data (header+7 rows per day for 7 days+weekly total row=51)

In Column B, labeled "Acuity" there are missing rows. If no patients for that acuity came in Clarity does not put a zero in for null data. Hence the missing rows. However, where the data is exported to I must have a row matching each acuity for each date with zeroes for all null values or else I get errors. So in Column B I go down the column and see that for each date it goes 1,2, 3, 4, 5,6, and T and then on the very last row in this column is the weekly total row which comes in blank but must have a capital "W" inserted.

I have attached two versions of the file. The first one is how the data comes to me. The second one I processed how I would normally do. Any rows or data manually added are highlighted in yellow to make it easier for you to see what I am referring to. I do not normally highlight any cells.

I want the macro not only for my use but so I can pass it on to some other less Excel savvy users in my organization who want the same report but lack the knowledge to do the manual steps I perform. So a macro which I can assign a shortcut key will make it easier to pass on. My attempts at trying to write this macro on my own were far from successful.

Thank you for your assistance.
Question by:KendallRN
  • 5
  • 5
LVL 101

Expert Comment

ID: 38803647
Have you tried recording the steps you take to do it manually as a macro?

Can the report be modified to provide the data required?


Author Comment

ID: 38803678
Since it isn't always the same rows needing  to be added recording a macro will not work. Some weeks I only need to add one row and other weeks I add 4 rows and they are not in the same place or for the same acuity. Sometimes it is missing an acuity 1 row for one date and then an acuity 5 row for another date. It is never missing an acuity "T" (Total) row for any date and there is always at least 3 rows of data for each date.

There needs to be certain amount of "logic" to check for what numbers are missing and add the appropriate rows. Also, recording a macro using another macro or add-in does not work as it does not capture those steps.

For cleaning up the extra spaces and non-printing characters this VBA code works:
Sub Clean_Trim()
    Dim CleanTrimRg As Range
    Dim oCell As Range
    Dim Func As WorksheetFunction
    Set Func = Application.WorksheetFunction
    On Error Resume Next
    Set CleanTrimRg = Selection.SpecialCells(xlCellTypeConstants, 2)
    If Err Then MsgBox "No data to clean and Trim!": Exit Sub
    For Each oCell In CleanTrimRg
        oCell = Func.Clean(Func.Trim(oCell))
End Sub

Open in new window

This is only part of the process. Finding where to insert rows and what date/acuity to assign automatically is a larger challenge programatically.

As for modifying the Crystal Report I have been told by our report writers it is not possible to have this done on the Crystal side. I had requested it when the report was built. I do not have access or knowledge to write the Crystal report directly.
LVL 101

Expert Comment

ID: 38803770
What is the issue?

Is it missing dates?
There are ways to add them in the report.

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 38803857
The issue is the missing rows of data. Yes, it needs to add the date, acuity, and a row of "0"s for the missing rows.

So the code would start by looking at B2 and check is the value 1? if Yes next cell is it's value 2 if yes then go to next cell and is it's value 3? If no then insert row and look one row down and to column A grab the date and put it in the inserted rows column A and in column B the value it was checking for that did not exist. Then for columns C through AA enter the value 0. Then continue with checking on the next row in column B to see if it's value is 4, then the next one 5, then the next one T (although the T row is never missing). Then repeat the process for a total of 7 cycles of this loop. Then after the 7th loop it would then grab the date from A2 and insert the date in A51 and put "W" in cell B51.

After that select cells A1:AA51 and run the Clean_Trim() code posted in my previous post. Set column A format to the short date format and then end the macro.

I can write the steps in English as above but not in VBA.
LVL 101

Expert Comment

ID: 38803876
It would be a lot easier and most likely more accurate to add the rows to the report.


Author Comment

ID: 38803905
I would definitely agree with you. However, when the report was built the report writer told me it was not possible for Crystal to add data that did not exist (null). This is a count of patient arrivals based on the date/time they arrived. If no patients arrive of a certain acuity within that day then the row does not exist. An acuity 6 is not really a valid acuity but represents those with no acuity assigned. Crystal is already doing that. The T is a total of acuities 1-6 or could also be seen as all arrivals regardless of acuity.

Since I am not a Crystal Report writer I have to rely on his expertise. I see you have lots of points for Crystal Software and know by far more how it works. I don't know if you have expertise in writing Crystal Reports from EPIC data? If you do then you may know how it can be done and could advise me what to suggest to our report writers.
LVL 101

Expert Comment

ID: 38803921
It is possible.  I have done it with much more complex data thn that.  it takes some ingenuity and ability to think outside the box.

So you have data like
Date         PatientId      Acuity
3 Jan           101                 1
3 Jan            102                 2


Author Comment

ID: 38803967
At the record level in EPIC the data would be:
Arrival Date/Time     PatientID   Acuity
1/13/2013 1000          34830      3
1/13/2013 0945           0483        4
1/13/2013  1005          08309      3
1/13/2013  1030          3587        2
1/13/2013   1045          9912       2

Currently the Crystal report would put that out as: (Count of patients for ea./hour/acuity)
Date       Acuity     09      10      11
1/13         2            0         2       0
1/13         3            0         2       0
1/13         4            1         0       0
1/13         T            1         4       0

What I am needing is this:
Date       Acuity     09      10      11
1/13          1            0         0       0
1/13          2            0         2       0
1/13          3            0         2       0
1/13          4            1         0       0
1/13          5            0         2       0
1/13          T            1         4       0
LVL 101

Accepted Solution

mlmcc earned 2000 total points
ID: 38804086
Something like this


Author Closing Comment

ID: 38827587
I am going to close this question and work with trying to get the report coming out of Crystal to better suit the need and decrease the need for a complicated macro.  Thanks for your help and timely responses, mlmcc.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question