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

avicric

New Member
Joined
Apr 24, 2017
Messages
32
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,081
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

avicric

New Member
Joined
Apr 24, 2017
Messages
32
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

New Member
Joined
Apr 24, 2017
Messages
32
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,081
Office Version
  1. 365
Platform
  1. Windows
What happens if you use this
VBA Code:
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
 
Solution

avicric

New Member
Joined
Apr 24, 2017
Messages
32
What happens if you use this
VBA Code:
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..... :love:

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

God Bless You ✌️👌🙌
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,081
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,136,639
Messages
5,676,935
Members
419,660
Latest member
Fred Cailloux

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top