Hi,
I have a code which loops through cells after applying autofilter which includes the unfiltered data.
After entering while loop, the line
Code
If dico_Triparty.Exists(CStr(wk.Sheets(1).Cells(i, nb_cl))) = True Thenis searching all unfiltered data and retrieving values to Triparty_pos file
Code
Set wk = Workbooks.Open(Pilot.Range("Mamout"))
i = 2
side_cl = Cells.Find("Side").Column
nb_cl = Cells.Find("Code").Column
Comment_cl = Cells.Find("Comment").Column
ActiveSheet.Range("A:AT").AutoFilter Field:=side_cl, Criteria1:="B"
While Not wk.Sheets(1).Cells(i, 1) = Empty
If dico_Triparty.Exists(CStr(wk.Sheets(1).Cells(i, nb_cl))) = True Then
ActiveSheet.Range("A:AT").AutoFilter Field:=nb_cl, Criteria1:=CStr(wk.Sheets(1).Cells(i, nb_cl))
If (CStr(wk.Sheets(1).Cells(i, nb_cl).Offset(0, -3))) = "Cash" Then
If Application.WorksheetFunction.Subtotal(3, Columns(nb_cl)) - 1 > 1 Then
Set rng = Range("G2:G" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
x = 0
For Each cll In rng
ReDim Preserve a(x)
a(x) = cll.Value
x = x + 1
Next
z = Join(a, ",")
Triparty_Pos.Cells.Find(CStr(wk.Sheets(1).Cells(i, nb_cl))).Offset(0, 5) = z
Else
Triparty_Pos.Cells.Find(CStr(wk.Sheets(1).Cells(i, nb_cl))).Offset(0, 5) = wk.Sheets(1).Cells(i, nb_cl).Offset(0, -1)
End If
ElseIf (CStr(wk.Sheets(1).Cells(i, nb_cl).Offset(0, -3))) = "Repo" Then
If Application.WorksheetFunction.Subtotal(3, Columns(nb_cl)) - 1 > 1 Then
Set rng = Range("D2:D" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
x = 0
For Each cll In rng
ReDim Preserve a(x)
a(x) = cll.Value
x = x + 1
Next
z = Join(a, ",")
Triparty_Pos.Cells.Find(CStr(wk.Sheets(1).Cells(i, nb_cl))).Offset(0, 5) = z
Else
Triparty_Pos.Cells.Find(CStr(wk.Sheets(1).Cells(i, nb_cl))).Offset(0, 5) = wk.Sheets(1).Cells(i, nb_cl).Offset(0, -4)
End If
Triparty_Pos.Cells.Find(CStr(wk.Sheets(1).Cells(i, nb_cl))).Offset(0, 6) = wk.Sheets(1).Cells(i, Comment_cl)
ActiveSheet.Range("A:AT").AutoFilter
End If
End If
i = i + 1
Wend