Excel macro : loop and append

Question

I am a total newbie when it comes to VBA and excel though, not to programming in general. It seems that I am having quite a time trying to decipher VBA.

What I want to do, is loop through an unknown number of rows in a column and then if it contains a specific acronym it will check the same row in a different column it will append one of two strings depending on the second cell's contents. This code doesn't seem to work but perhaps it will help elucidate what I'm getting at.

Sub AppendMacro()
'
'Append Macro
'Keyboard Shortcut: Ctrl+l
'
Dim c As Range

For Each c In Range("S:S")

    If c.Value = "USAA" Or c.Value = "U.S.A.A" Then

    ActiveCell.Offset(0, 2).Select

     If ActiveCell.Value = "AM" Then

       ActiveCell.Value = ActiveCell.Value & "8-10"

       End If

    End If
Next c

End Sub

One thing I know is that the value of the cell isn't exactly going to be USAA or U.S.A.A but will contain those sets of characters. Also, I'm sure I'm misunderstanding how ActiveCell works but if anything will set me straight it'll be you guys.

Jean-François Corbett · Accepted Answer · 2011-07-13 06:45:59Z

The only reason your code didn't work is because you forgot to activate the appropriate cell before calling ActiveCell.

If c.Value = "USAA" Or c.Value = "U.S.A.A" Then
    c.Activate 'Aha!
    ActiveCell.Offset(0, 2).Activate
    [etc.]

Of course, as pointed out by @GSerg, this Select and ActiveCell business is bad practice and makes your life more complicated. @GSerg's solution works fine.

You mention that

the value of the cell isn't exactly going to be USAA or U.S.A.A but will contain those sets of characters.

Therefore I thought it would be appropriate to show how you can anticipate typos and make your code accept things like "US A A", "U.S.A,A" or "U..S.A.. ,A".

Dim c As Range
Dim s As String

For Each c In Range("S:S").Cells
    ' Get the cell content
    s = c.Value

    ' Cleanse it of "noise" characters
    s = Replace(s, ".", "")
    s = Replace(s, ",", "")
    s = Replace(s, " ", "")
    s = Replace(s, "whatever other characters may pollute USAA", "")

    ' Does the cleansed string contain "USAA"?
    If InStr(s, "USAA") <> 0 Then ' wink to @Issun
        With c.Offset(0, 2)
            If .Value = "AM" Then .Value = .Value & "8-10"
        End With
    End If
Next c

GSerg · Accepted Answer · 2011-07-12 21:18:51Z

2

Dim c As Range
For Each c In Range("S:S").Cells
    If instr(c.Value, "USAA") > 0 Or instr(c.Value, "U.S.A.A") > 0 Then
        With c.Offset(0, 2)
            If .Value = "AM" then .Value = .Value & "8-10"
        End With
    End If
Next c

InStr finds first instance of a string inside another string.
ActiveCell is the currently selected cell in the active window, not the loop counter. It is advisable to avoid using ActiveCell (and .Select) in code, unless you actually want the user to select a cell and then act upon it.
With...End With is just a handy way to temporarily capture a reference to c.Offset(0, 2), to avoid explicit variable for it or calling it three times in a row. Without this block, it would be
```
If c.Offset(0, 2).Value = "AM" Then
    c.Offset(0, 2).Value = c.Offset(0, 2).Value & "8-10"
End If
```

edited Jul 12, 2011 at 21:18

answered Jul 12, 2011 at 21:13

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

2 Comments

Gaijinhunter Over a year ago

Very nice code! If I could suggest it, I've recently noticed that when using Instr, it's faster to check for innequality than if the value is greater than 0 so you can use "<> 0" instead of "> 0" for a slight speed boost. Granted it only really makes a difference when using the code on a large number of values.

Patrick Honorez Over a year ago

and adding .SpecialCells(xlCellTypeConstants) in the second line will avoid looping through the empty cells of the column, saving some processing as well

Collectives™ on Stack Overflow

Excel macro : loop and append

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related