searching a string and adding to it

[dorsey]

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]

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]

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]

@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]

or

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

[Paul_Hossler]

@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