PDA

View Full Version : searching a string and adding to it



dorsey
10-09-2013, 07:30 PM
First off, thanks for taking the time to read this and respond if you do.

I am working on a task where I have multiple workbooks that are similar. Each workbook has column populated with state abbreviations. IA, IN, AL, AZ ..etc. I am trying to create a macro that will search cell in the column for IN, or IN , and if the cell has the string then add MI.

I have very rudimentary VB skills - basically I searched and found script that could find it using instr command. I can post this tomorrow night but I will try to give it my best shot here.


Sub - Code
Dim i
For i = 1 to 30
Instr(UseCase(Cells,i, "IN, ") then
ActiveCell = ActiveCell & ",MI"
End Step
End Sub
30 ,
Sorry as im sure this isnt exactly how it looks but its similar. right now it reads all 30 cells ad places 30 ,MI at the end of the string in A1 only. The column all be searching could be 1000 cells so the cell count For i statement may not be necessary. just the first thing I found that work.

Any help is appreciated.

Paul_Hossler
10-09-2013, 07:56 PM
Put this into a standard module.

The basic process is

1. Create a range from Col A (change it to suit) of all the cells that have a constant text data in them. Get out if there are none

2. Look at each of the cells from 1) for IN, or IN ,

3. Add ,MI if 2) is in the cell

Check online help for the details of the statements, or post a question

Good luck



Option Explicit
Sub AddMI()
Dim rSearch As Range, rCell As Range
Set rSearch = Nothing
On Error Resume Next
Set rSearch = Worksheets("Sheet1").Range("A:A").SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0

If rSearch Is Nothing Then Exit Sub

Application.ScreenUpdating = False

For Each rCell In rSearch.Cells
If InStr(1, rCell.Value, "IN,", vbTextCompare) > 0 Then
rCell.Value = rCell.Value & ",MI"
ElseIf InStr(1, rCell.Value, "IN ,", vbTextCompare) > 0 Then
rCell.Value = rCell.Value & ",MI"
End If
Next

Application.ScreenUpdating = True

MsgBox "All Done"

End Sub




If (when) you need to update it, the changes should be pretty easy for you to figure out -- just follow the basic approach


Paul

dorsey
10-09-2013, 08:28 PM
Thank you very much for the explanation. I was definitely a lot farther off then I thought. Can't wait to try this tomorrow. I'll post again when I've got it working. Thanks again

Doug

Aussiebear
10-10-2013, 01:34 AM
@Paul, Not sure that I follow the logic of step 2 "Look at each of the cells from 1) for IN, or IN ,", is the string just IN or For IN or Or IN? I'm guessing it is simply "IN" given that is string that you appear to be searching for, or is it simply a typo in step 2.

snb
10-10-2013, 03:27 AM
or

Sub M_snb()
columns(1).replace "IN","IN ",1
columns(1).replace "IN ","IN MI",1
end sub

Paul_Hossler
10-11-2013, 06:39 AM
@Aussiebear




search cell in the column for IN, or IN , and if the cell has the string then add MI.


The OP's requirements (above) were a little fuzzy to me, so I very likely might have made a typo or misunderstood

I took it to mean "IN<comma>" or "IN<space><comma>"

If so, add "<comma>MI" to the end (since the OP's sample code had the <comma> even if the requirements didn't)

Along those lines, the sample code had "IN<comma><space>" but by then the batteries in my telepathy helment were getting low, so I made the code very streight forward to allow the OP to edit just in case


Paul