How to replace values in cell?

[bnageshrao]

I have a spreadsheet and two of the columns are called Lead Name and AM Name

Lead Name AM Manager
1. InValid Invalid
2. Invalid (Blank)
3. Rick Hu Thomas King
4. (Blank) John Reid

I have to update the Lead Name Column and the criteria is if the Lead Name has a value of Invalid or if it is Blank, then I have to replace those values with the first AM Manager name value. If the first AM manager name is either invaild or Blank, then I have to choose the next AM manager's value. So what I want to achieve in those columns after the update is as follows.

Lead Name AM Manager
1. Thomas King Invalid
2. Thomas King (Blank)
3. Rick Hu Thomas King
4. Thomas King John Reid

How I can do this by Using VBA in excel. I know I have to use some sort of loop, but I have not any program in VBA, so a small code will help me. Thanks in advnace

 

[PHV]

A starting point:
Dim LastRow As Long, AM As String
LastRow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
With Columns("B:B")
.AutoFilter Field:=1, Criteria1:="<>invalid", Operator:=xlAnd, Criteria2:="<>"
AM = Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible).Cells(1, 1).Value
.AutoFilter
End With
With Columns("A:A")
.AutoFilter Field:=1, Criteria1:="=invalid", Operator:=xlOr, Criteria2:="="
Range("A2.A" & LastRow).SpecialCells(xlCellTypeVisible).Value = AM
.AutoFilter
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vbap]

Hey PH,

Very cool little routine. Not ever thought of that approach.