Looping through Data

[jwburritt]

Hi: I have data in col A and B. I'd like to build a macro that, if it finds the term "trigger" in col A, it copies the data in col B to col C (all in the same row). But, if it doesn't find the term "trigger" in col A, it copies the prior successful data to col C. Here's what I have so far ...

Sub Data_Copy()

    
    Dim i As Long
    Dim myValue As String
    Dim LastRow As Long
    Dim StartRow As Long
    
    StartRow = 1
    LastRow = Range("A" & StartRow).End(xlDown).Row
    
    For i = StartRow To LastRow
        myValue = Range("A" & i).Value
        If myValue = "Trigger" Then
            Range("C" & i).Value = Range("B" & i).Value
            Else: Range("C" & i).Value = myValue
        End If
    Next i
    


End Sub

[TABLE="width: 482"]
<tbody>[TR]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Col C[/TD]
[/TR]
[TR]
[TD]Trigger[/TD]
[TD]Blue[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]Not Trigger[/TD]
[TD]Yellow[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]Not Trigger[/TD]
[TD]Red[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]Trigger[/TD]
[TD]Green[/TD]
[TD]Green[/TD]
[/TR]
</tbody>[/TABLE]

Thank you!

 

[Fluff]

How about

Sub jwburritt()
   Dim Cl As Range
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Replace "Trigger", "=xxxTrigger", xlWhole, , False, , False, False
      For Each cl In .SpecialCells(xlFormulas, xlErrors)
         cl.Offset(, 2).Value = cl.Offset(, 1).Value
      Next cl
      .Offset(, 2).SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      .Offset(, 2).Value = .Offset(, 2).Value
      .Replace "=xxxTrigger", "Trigger", xlWhole, , False, , False, False
   End With
End Sub

 

[jwburritt]

Thanks. Works great!

 

[Fluff]

Glad to help & thanks for the feedback

 

[Rick Rothstein]

Here is another way to consider (better late than never)...

Sub Data_Copy()
  Dim i As Long
  For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
    If LCase(Cells(i, "A").Value) = "trigger" Then
      Cells(i, "C").Value = Cells(i, "B").Value
    Else
      Cells(i, "C").Value = Cells(i - 1, "C").Value
    End If
  Next
End Sub

Note: This code assume cell A1 will always contain the word "Trigger"

 

[jwburritt]

Works great. Just had to move the data down so the first column begins at A2 and the counter begins at 2. Otherwise this line

Cells(i, "C").Value = Cells(i - 1, "C").Value

would throw a 1004 error.

Thanks again!