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
Solved

VBA for Excel Spreadsheet

Posted on 2011-03-16
11
380 Views
Last Modified: 2012-05-11
Hello,

Could someone tell me how to find all of the records in a column that start with the letter 'A', and if they start with 'A' do a mid function.  =mid(A2, 2, 5)

Thanks.
0
Comment
Question by:erp1022
11 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 35151156
You could put a formula on a second column like this:

=IF(LEFT(A1,1)="A",MID(A1, 2, 5),"")

This will extract the information that you want. Then you could Autofilter this column to list all excepet blanks and copy the result to a new sheet.

jppinto
0
 

Author Comment

by:erp1022
ID: 35151212
I actually need VBA for this. It's for an end user and I would like for them to be able to just press a button. (run a macro)
0
 
LVL 4

Assisted Solution

by:MeLindaJohnson
MeLindaJohnson earned 50 total points
ID: 35151580
Record a macro. -->Developer tab, Record Macro
Walk through the steps. (Find the A, and the replace with what you what)
Stop the macro
This will produce the vba code for you.--Developer tab, Visual Basic, vbaproject(personal.xlsb)
Then you can copy the macro code into their personal.xlsb file (after tweeking a little bit probably
and then reference it on the toolbar when you create the button -> right click on toolbar , customize quick access toolbar, choose commands from: choose macros-> choose the name of the macro and add it, click ok.
Good luck
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:erp1022
ID: 35155885
That won't work either. There will be a different number of records in my file each month. What I need is VBA that will go through the whole column and if it begins with an A, strip it off, then do the mid function.
0
 
LVL 4

Expert Comment

by:MeLindaJohnson
ID: 35156311
If you highlight the whole column before performing the find function, that should help, that way it will only look in that column
0
 
LVL 4

Expert Comment

by:MeLindaJohnson
ID: 35156316
please provide an excel file
0
 

Author Comment

by:erp1022
ID: 35156351
Here's what the VBA looks like after recording the macro. In the 2nd to last and last line where it says 'Range' can I just change that to B2:B2 and that will take care of the whole column no matter how many records?
Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=IF(LEFT(RC[1],1)=""A"", MID(RC[1], 2, 5), RC[1])"
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B2204")
    Range("B2:B2204").Select
End Sub
0
 
LVL 4

Expert Comment

by:MeLindaJohnson
ID: 35156408
Not B2:B2, but you could do
B2:B(choose a number that you think would be the maximum number of rows like 60,000)
0
 

Author Comment

by:erp1022
ID: 35156935
There should be some function, I'm not a VBA developer (obviously) but I think it's transpose or array or count? Something that will count the actual number of records and update only that range. I really don't know how many records my file will be and don't like putting in a random number to account for the range.
0
 
LVL 1

Accepted Solution

by:
martyk earned 450 total points
ID: 35190979
See if this works for you.

 
' What I need is VBA that will go through the whole column
' and if it begins with an A, strip it off, then do the mid function.
'  =mid(A2, 2, 5)

Public Sub DeletePrefix()

For Each i In Sheets("sheet1").Range("A:A") ' entire 1st column independent of max rows
    If Mid(i.Value, 1, 1) = "A" Then
        i.Value = Mid(i.Value, 2, 5) 'NOTE: remove last argument to preserve all but the first character
    End If
Next i

End Sub

Open in new window


Marty
0
 

Author Comment

by:erp1022
ID: 35241566
Sorry for the delay, I just got back from vacation. I will test this either later on today or early tomorrow.

Thanks.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Suggested Solutions

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
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.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

809 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