Solved

Need a Macro that Loops and Performs Functions in Excel 2010

Posted on 2013-06-13
9
149 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 46

Expert Comment

by:Martin Liss
ID: 39245041
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
ID: 39245044
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
ID: 39245048
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 250 total points
ID: 39245058
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
 
LVL 26

Accepted Solution

by:
redmondb earned 250 total points
ID: 39245128
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 46

Expert Comment

by:Martin Liss
ID: 39251459
Did any of the above help you?
0
 

Author Closing Comment

by:esu4236
ID: 39253073
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 46

Expert Comment

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

Marty - MVP 2009 to 2013
0
 
LVL 26

Expert Comment

by:redmondb
ID: 39253243
Thanks, esu4236.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

840 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