• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 489
  • Last Modified:

Excel VBA Macro to prepare data for export

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.
  • 5
  • 5
1 Solution
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?

KendallRNAuthor Commented:
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.
What is the issue?

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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

KendallRNAuthor Commented:
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.
It would be a lot easier and most likely more accurate to add the rows to the report.

KendallRNAuthor Commented:
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.
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

KendallRNAuthor Commented:
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
Something like this

KendallRNAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now