Solved

VBA for Excel Spreadsheet

Posted on 2011-03-16
11
379 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
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 demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

777 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