Replace

[scott]

I have a macro that does a filter on Column L for Sale Rep "Joe" and
then tries to replace the values in Column A. The problem is that is
replaces all instances in Column A, not just the filtered, or visible cells.

I tried this macro, an some variations, but have not had any success:

Sub UpdateCustType()

Dim mycell As Range
Dim myrng As Range

Set myrng = Range("L2", Selection.End(xlDown)) 'Loop through Sales Reps
For Each mycell In myrng
If Cell.Value = "Joe" Then
Cell.Offset(0, -11).Value = "Ind Contractor"
End If
Next mycell

End Sub

Any help would be appreciated

Thanks,

Scott

 

[Tom Ogilvy]

The code you show shouldn't do anything except raise an error since CELL is
not initialized.

Sub UpdateCustType()

Dim mycell As Range
Dim myrng As Range

Set myrng = Range("L2", Range("L2").End(xlDown)) 'Loop through Sales Reps
For Each mycell In myrng
If lcase(myCell.Value) = "joe" Then
myCell.Offset(0, -11).Value = "Ind Contractor"
End If
Next mycell
End Sub

 

[scott]

Thanks Tom,works great.