Solved

Need a Macro that Loops and Performs Functions in Excel 2010

Posted on 2013-06-13
9
146 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
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…
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

863 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

18 Experts available now in Live!

Get 1:1 Help Now