Dear all,
I'm trying to loop through Filtered Range of Visible Cells. Unfortunately, I'm just able to
I'm looping through names of players ( Players sheet) and mapping their clubs (Player sheet).
The mapping just doesn't seem to work for 1st row of data (except header) i.e for Player " B "
PFA for sample data.
Here's the code logic.
Sub Club_Mapping()
Sheets("Players").Select
Range("A1").Select
' Look for Name column
Cells.Find(What:="Name", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Select
Dim name As String
Dim club As String
Do Until IsEmpty(ActiveCell)
name = ActiveCell.Value
club = ActiveCell.Offset(0, 1).Value
Sheets("Player").Select
Range("A1").Select
'Filtering to get the required rows
Dim plRange As Range
' Look for Player column
Set plRange = Cells.Find(What:="Player", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
'Mention your criteria for the Filter
Selection.AutoFilter
ActiveSheet.Range("$A$1:$C$1").AutoFilter Field:=plRange.Column, Criteria1:=name
Dim playerRange As Range
Set playerRange = Range("B2:B" & Range("B65536").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
'Looping Filtered Range
For Each p In playerRange
p.Select
ActiveCell.Offset(0, 1).Value = club
Next
Sheets("Players").Select
ActiveCell.Offset(1, 0).Select
Loop
Range("A1").Select
ActiveWorkbook.Save
MsgBox "Done"
End Sub
Appreciate your help in correcting me and helping me understand the mistake.
Thanks,
Vinod Krishna
Bookmarks