Solved

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

Posted on 2014-03-05
4
356 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 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: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

Technology Partners: 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!

Question has a verified solution.

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

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

763 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