Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need a Macro that Loops and Performs Functions in Excel 2010

Posted on 2013-06-13
9
Medium Priority
?
154 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 49

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 49

Assisted Solution

by:Martin Liss
Martin Liss earned 1000 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 1000 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 49

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 49

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

721 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