VBA for Excel Spreadsheet

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.
erp1022Asked:
Who is Participating?
 
martykConnect With a Mentor Commented:
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
 
jppintoCommented:
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
 
erp1022Author Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
MeLindaJohnsonConnect With a Mentor Commented:
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
 
erp1022Author Commented:
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
 
MeLindaJohnsonCommented:
If you highlight the whole column before performing the find function, that should help, that way it will only look in that column
0
 
MeLindaJohnsonCommented:
please provide an excel file
0
 
erp1022Author Commented:
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
 
MeLindaJohnsonCommented:
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
 
erp1022Author Commented:
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
 
erp1022Author Commented:
Sorry for the delay, I just got back from vacation. I will test this either later on today or early tomorrow.

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.