VBA Macro help

[Street Hunter]

I need help in creating a Macro where:

• I have to Select PAYMENTS Tab.
• Filter Data in REFERENCE COLUMN with the name AXEL in ROW 5.
• Assign Unique sequential INVOICE NMB to each filtered entry, for e:g (Comp-01, Comp-02)

 

[Hui]

Street Hunter

Firstly, Welcome to the Chandoo.org Forums

Try the following code:

Sub Filter_Assign_Values()

  'Setup
  Dim FilterStr As String
  FilterStr = "Axel"

  'Select Worksheet
  Sheets("Payments").Select

  'Clear Filter
  On Error Resume Next
  ActiveSheet.ShowAllData

  'Apply "Axel" Filter

  Dim rng As Range
  Dim lr As Long
  lr = Range("A" & Rows.Count).End(xlUp).Row
  Set rng = Range("A5:F" & lr)
  rng.AutoFilter Field:=2, Criteria1:=FilterStr

  'assign values to Filtereed data
  Dim cl As Range
  Dim x As Integer
  Set rng = Range("F6:F" & lr)

  x = 1

  For Each cl In rng.SpecialCells(xlCellTypeVisible)
  cl = "Comp-" & Format(x, "00")
  x = x + 1
  Next cl

End Sub

or see the attached file

 

[Street Hunter]

Many Thanks Hui,
You really did an awesome job.