Solved

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

Posted on 2014-03-05
4
353 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
  • 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

770 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