VBA Code : Based on filter selected need to make changes in column

[avicric]

Hi All,

Need your help with VBA code, I have to make changes to a column based on the filter selected in another column.

So I have to put/select filter in "column E" based on the filtered item need to rename all items in "column S"...

I have managed the below code but if 1 of the item is missing from the filter in "column E" the VBA just hangs....

Selection.AutoFilter
ActiveSheet.Range("$A:$V").AutoFilter field:=5, Criteria1:= _
"Assigned to Finance"
Range("S1").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
Range("S1").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
Range(Selection, Selection.End(xlDown)).Select
Selection.FormulaR1C1 = "Finance"

Thanks for your help in advance.

 

[Fluff]

What was the error?
Also if you were getting the error on that line, why did you leave that in the code & changed the subsequent line?

 

[avicric]

What was the error?
Also if you were getting the error on that line, why did you leave that in the code & changed the subsequent line?

1) there is no error message I get in VBA..... the line is only highlihted

2) as the happened I tweaked the code with my version(which has part of your code given)....

 

[avicric]

1) there is no error message I get in VBA..... the line is only highlihted

2) as the happened I tweaked the code with my version(which has part of your code given)....

I tried your code with all my requirements, now and I am getting "Runtime error 6 Overload" on the same line.......

 

[Fluff]

What happens if you use this

Sub avicric()
   Dim UsdRws As Long
   Dim Ary1 As Variant, Ary2 As Variant
   
   Ary1 = Array("Bank details required", "Awaiting documents/payment from customer", "Visit not Confirmed - Customer Delay", "Visit failed - Customer unavailable")
   Ary2 = Array("Same/Equi Device Booking Initiated", "Replacement - Customer Approval Pending", "Advance Payment Before Repair", "Reestimate - Pending Approval", "Pending Approval - Estimate received")
   With ActiveSheet
      UsdRws = .Range("E" & Rows.Count).End(xlUp).Row
      .Range("A1:V" & UsdRws).AutoFilter 5, "Assigned to Finance"
      If .Range("A1:A" & UsdRws).SpecialCells(xlVisible).Count > 1 Then
         .AutoFilter.Range.Offset(1).Resize(UsdRws - 1).Columns(19).Value = "Finance"
      End If
      .Range("A1:V" & UsdRws).AutoFilter 5, Ary1, xlFilterValues
      If .Range("A1:A" & UsdRws).SpecialCells(xlVisible).Count > 1 Then
         .AutoFilter.Range.Offset(1).Resize(UsdRws - 1).Columns(19).Value = "CC Team"
      End If
      .Range("A1:V" & UsdRws).AutoFilter 5, Ary2, xlFilterValues
      If .Range("A1:A" & UsdRws).SpecialCells(xlVisible).Count > 1 Then
         .AutoFilter.Range.Offset(1).Resize(UsdRws - 1).Columns(19).Value = "Replacement"
      End If
      .AutoFilterMode = False
   End With
End Sub

 

[avicric]

What happens if you use this

Sub avicric()
   Dim UsdRws As Long
   Dim Ary1 As Variant, Ary2 As Variant
  
   Ary1 = Array("Bank details required", "Awaiting documents/payment from customer", "Visit not Confirmed - Customer Delay", "Visit failed - Customer unavailable")
   Ary2 = Array("Same/Equi Device Booking Initiated", "Replacement - Customer Approval Pending", "Advance Payment Before Repair", "Reestimate - Pending Approval", "Pending Approval - Estimate received")
   With ActiveSheet
      UsdRws = .Range("E" & Rows.Count).End(xlUp).Row
      .Range("A1:V" & UsdRws).AutoFilter 5, "Assigned to Finance"
      If .Range("A1:A" & UsdRws).SpecialCells(xlVisible).Count > 1 Then
         .AutoFilter.Range.Offset(1).Resize(UsdRws - 1).Columns(19).Value = "Finance"
      End If
      .Range("A1:V" & UsdRws).AutoFilter 5, Ary1, xlFilterValues
      If .Range("A1:A" & UsdRws).SpecialCells(xlVisible).Count > 1 Then
         .AutoFilter.Range.Offset(1).Resize(UsdRws - 1).Columns(19).Value = "CC Team"
      End If
      .Range("A1:V" & UsdRws).AutoFilter 5, Ary2, xlFilterValues
      If .Range("A1:A" & UsdRws).SpecialCells(xlVisible).Count > 1 Then
         .AutoFilter.Range.Offset(1).Resize(UsdRws - 1).Columns(19).Value = "Replacement"
      End If
      .AutoFilterMode = False
   End With
End Sub

what happens is that it works like a charm.....

my sincere apologies for not providing the correct details the first time......?

God Bless You ??

 

[Fluff]

You're welcome & thanks for the feedback.