Solved

Need a Macro that Loops and Performs Functions in Excel 2010

Posted on 2013-06-13
9
145 Views
Last Modified: 2013-06-17
I don't know enough about macros to know where to start with this, but here's what I need in the attached file.  Hopefully someone can help me.

On column I, the following needs to be done:

1.  Loop through the column until no values are found
2.  Perform the following functions
     a.  Remove/delete the first 3 characters
     b.  Next, if there is a leading space after deleting the first 3 characters, delete the leading space.
     c.  Then delete the last 8 characters (is a date) plus the space before the date.
Kathy-Kelley-example.xls
0
Comment
Question by:esu4236
9 Comments
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
The attached workbook contains a macro named 'Del' that does the job. The macro is assigned to Ctrl+z so if you want to do it again type press Ctrl+z.
Kathy-Kelley-example.xls
0
 
LVL 24

Expert Comment

by:Steve
Comment Utility
The following should do it:

Sub remove()

For x = 2 To Range("I65000").End(xlUp).Row

cellvalue = Trim(Cells(x, "I"))
cellvalue = Right(cellvalue, Len(cellvalue) - 3)
cellvalue = Left(cellvalue, Len(cellvalue) - 8)
Cells(x, "I") = Trim(cellvalue)

Next x

End Sub

Open in new window

Kathy-Kelley-example.xls
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Hi, esu4236.

Please attached. A couple of points...
(1) There are trailing blanks in the data so my macro strips them.
(2) The code assumes that each cell has "valid data". If not, then you will get an error message. Let me know if you would prefer that the macro to terminate cleanly with an error message or to quietly complete without an error.

The code is...
Sub Reformat_Data()
Dim xLast_Row As Long
Dim xCell     As Range
Dim xHold     As Variant

Sheets("Sheet1").Activate

xLast_Row = ActiveSheet.UsedRange.Cells(1, 1).Row + ActiveSheet.UsedRange.Rows.Count - 1
If xLast_Row < 2 Then
    MsgBox ("No data found in " & ActiveSheet.Name & " - run cancelled.")
    Exit Sub
End If

For Each xCell In Range("I2:I" & xLast_Row)
    xHold = xCell
    xHold = Trim(Mid(xHold, IIf(Mid(xHold, 4, 1) = " ", 5, 4), 9999))
    xHold = Mid(xHold, 1, Len(xHold) - 9)
    xCell = xHold
Next

End Sub

Open in new window

Regards,
Brian.
Kathy-Kelley-example-V2.xls
0
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 250 total points
Comment Utility
In mine if you have a lot of data and you want to speed it up then change the macro to


Sub Del()
Dim lngLastRow As Long
Dim lngRow As Long
lngLastRow = Range("I65536").End(xlUp).Row

Application.ScreenUpdating = False

For lngRow = 2 To lngLastRow
    Cells(lngRow, 9).Value = Mid(Cells(lngRow, 9).Value, 4)
    If Left(Cells(lngRow, 9).Value, 1) = " " Then
        Cells(lngRow, 9).Value = Mid(Cells(lngRow, 9).Value, 2)
    End If
    Cells(lngRow, 9).Value = Mid(Cells(lngRow, 9).Value, 1, InStr(1, Cells(lngRow, 9), " ") - 1)
Next

Application.ScreenUpdating = True

End Sub

Open in new window

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 26

Accepted Solution

by:
redmondb earned 250 total points
Comment Utility
esu4236,

Martin's point about speeding things up is well-made. Depending on the number of rows , there are a number of other speed-boosts which may be worthwhile. (They'll definitely speed things up, but the extra complexity would be pointless for a small file.)

Edit:
Please see the attached. A couple of points...
(1) I noticed that there were two spaces before the date. I'm guessing that you didn't want them, so any spaces immediately before the date are now stripped.
(2) Invalid data no longer causes an error message. Instead, a message is displayed when the run completes specifying the number of errors found. (Note that the first 3/4 characters will still be stripped from these entries.)

Regards,
Brian.Kathy-Kelley-example-V3.xls
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Did any of the above help you?
0
 

Author Closing Comment

by:esu4236
Comment Utility
Yes it did!!!  Sorry I was out of the office on Friday.  

I am splitting the points between MartinLiss and Brian Redmond.  You were both VERY helpful, and it seems that the macro is working as it should.  Thank you so much for your prompt help on this.  And sorry I was not able to get back to you right away.  Hope you had a great weekend!!
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, esu4236.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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 article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

772 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

11 Experts available now in Live!

Get 1:1 Help Now