?
Solved

Macro to Format Data is Failing to Use All of the Data

Posted on 2014-03-05
4
Medium Priority
?
372 Views
Last Modified: 2014-03-06
Hello Experts,

I have had some fantastic input with the macros which help me automate some of my audit reporting by heavily formatting the imported data into something a bit more useful.

I am having a bit of a problem with one of the macros.  Macro 1 - FormatImportText - brings the raw data and formats it into 5 columns rather than spread out over 6.

Macro 2 - FormatText - moves the columns around and formats the data further.

In the attached example, the macro is only formatting 5 of the rows rather than 12.  I have been staring at it and playing around with it but I have had no luck so far.

Would someone please take a look at the macros and see what I am failing to see?

Any help you could give me would be greatly appreciated.

Regards,
Infosec36
Macro-Example.xlsm
0
Comment
Question by:Sonia Bowditch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39907656
infosec36,

Honestly, some part of the macro doesn't seem to make any sense.

For example, the line
ReDim rng(0 To UBound(vData) \ 3, 1 To 5)

Open in new window

The UBound of vData is 15. In this line, the new Variant array is set to only 5 rows because of this line. (15/3 = 5).
Why would it divide the total number of data rows by 3?
0
 
LVL 12

Accepted Solution

by:
Harry Lee earned 2000 total points
ID: 39907720
Try changing the 2nd VBA macro to the following to see if that's what you want.

Sub FormatText()

Dim vData As Variant
Dim rng() As Variant
Dim x As Integer, y As Integer

With Sheets("Format").UsedRange
    ' round up to multiple of 3
    vData = .Resize(((.Rows.Count - 1) \ 3) * 3 + 3).Value
End With
' row 0 for headers, 1-n for data
ReDim rng(0 To UBound(vData), 1 To 5)

rng(0, 1) = "Host Name"
rng(0, 2) = "Severity"
rng(0, 3) = "EventId"
rng(0, 4) = "Date"
rng(0, 5) = "Message"

For x = 1 To UBound(vData) Step 1
    rng(x, 1) = vData(x, 1)
    rng(x, 2) = vData(x, 2)
    rng(x, 3) = vData(x, 3)
    rng(x, 4) = vData(x, 5)
    rng(x, 5) = vData(x, 4)
Next x

Sheets("Formatted").Cells(1, 1).Resize(UBound(rng) + 1, UBound(rng, 2)).Value = rng
Sheets("Formatted").Rows("2").Delete
Sheets("Formatted").Columns("A:E").ColumnWidth = 100
Sheets("Formatted").Columns.AutoFit
Sheets("Formatted").Rows.AutoFit

End Sub

Open in new window

0
 

Author Closing Comment

by:Sonia Bowditch
ID: 39908757
Thank you so much Harry.  It works exactly the way I wanted.  The original macro was a hold over from when the data was in a slightly different format.

Again, thank you very much for you help.

Regards,
InfoSec36
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39909966
infosec36, you are welcome! I'm glad I can help.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
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 Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

771 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