Solved

VBA for Excel Spreadsheet

Posted on 2011-03-16
11
377 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
 

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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,…
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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…

744 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