Solved

macro to Modify Excel Spreadsheet

Posted on 2016-09-22
4
38 Views
Last Modified: 2016-09-22
Hello,

I would like to modify a Spreadsheet that is preconfigured to export from the software in the below way:

Excel 1 non formattte

then I would like it to format in the following way:

Excel 2 Formatted

The person may have more than one Badge ID so it could be 2 or 3 or 4 like the Cray, Acie has, it just depends.

don't know if there is a way to produce this?  Maybe by a macro or VBA? something?  I have perhaps, thousands.
0
Comment
Question by:Ernest Grogg
4 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41811604
Please post a sample workbook.
0
 
LVL 8

Expert Comment

by:Koen
ID: 41811637
try this:

Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("Sheetname").Select
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Name"
    Range("B2").Select
    Do While ActiveCell.Value <> "" And ActiveCell.Offset(1, 0).Value <> ""
        Selection.Cut
        ActiveCell.Offset(0, -1).Range("A1").Select
        ActiveSheet.Paste
        ActiveCell.Offset(0, 1).Range("A1:F1").Select
        Selection.Delete Shift:=xlUp
        Do While ActiveCell.Offset(1, 0) <> ""
            ActiveCell.Offset(0, -1).Range("A1").Select
            Selection.Copy
            ActiveCell.Offset(1, 0).Range("A1").Select
            ActiveSheet.Paste
            ActiveCell.Offset(0, 1).Range("A1").Select
        Loop
        ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
        Selection.Delete Shift:=xlUp
        ActiveCell.Offset(0, 1).Range("A1").Select
    Loop
    
    End Sub

Open in new window

0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 41811639
This code will parse your data.  It creates a new sheet with a name column, names for each Badge ID, and no blank rows.
Sub Parse_Badges()
    Dim lngLastRow As Long
        
    'Test that the active sheet is not already processed - or not expected layout
    If Range("A1").Value <> "Badge ID" Then
        MsgBox "Data set does not appear to be correct; Halting process.", vbCritical + vbOKOnly, "Error"
        Exit Sub
    End If
    
    'Test if there is already an existing parsed sheet in the workbook
    For sh = 1 To ActiveWorkbook.Sheets.Count
        If Sheets(sh).Name = "Badge List" Then
            MsgBox "There is already a processed sheet.  Please delete it then re-run.", vbCritical + vbOKOnly, "Badge List Already Exists"
            Exit Sub
        End If
    Next sh
    'Make a copy of the current sheet and process
    ActiveSheet.Copy before:=Sheets(1)
    Sheets(1).Name = "Badge List"
    
    lngLastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Value = "Name"
    Range("A1").Font.Bold = True
    Range("A2").Select
    Do Until ActiveCell.Row = lngLastRow
        If InStr(1, ActiveCell.Offset(0, 1).Value, ",", vbTextCompare) > 0 Then
            strName = ActiveCell.Offset(0, 1).Value
            ActiveCell.EntireRow.Delete
            lngLastRow = lngLastRow - 1
            ActiveCell.Value = strName
            ActiveCell.Offset(1, 0).Select
        ElseIf ActiveCell.Offset(0, 1) = "" Then
            ActiveCell.EntireRow.Delete
            lngLastRow = lngLastRow - 1
        Else
            ActiveCell.Value = strName
            ActiveCell.Offset(1, 0).Select
        End If
    Loop
    Range("A1").Select
    Selection.AutoFilter
    Columns("A:G").EntireColumn.AutoFit
    Range("A2").Select
    MsgBox "Done."
End Sub

Open in new window


I've attached an example file.  To test it, run on the "Original" sheet; it will create a new one called "Badge List".
EE-CreateBadgeList.xlsm
0
 

Author Closing Comment

by:Ernest Grogg
ID: 41811761
Works good.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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.

758 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

19 Experts available now in Live!

Get 1:1 Help Now