Solved

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

Posted on 2014-03-05
4
351 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:infosec36
  • 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 500 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:infosec36
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
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 Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

919 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now