Solved

VBA for Excel Spreadsheet

Posted on 2011-03-16
11
378 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

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

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
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 …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

911 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

24 Experts available now in Live!

Get 1:1 Help Now