0

I have this excel sheet: enter image description here

i used vba to filter the sheet. however, i would like to change the value of the column M, the selected column in the picture. my problem is when cant get a way to select hese two cells only which are the result of the filter. i came across a lot of solutions with xltypevisible but that didn't work and it's still selecting everything to the end of the sheet including the filtered ones as well, and changing the value of over 1 million cells.

my code

For Each c In ws.Range("A2:A" & rws)
On Error Resume Next
docnum = c.Value
docdt = c.Offset(0, 2).Value
posamt = c.Offset(0, 1).Value
With rngToFilter
 .AutoFilter Field:=3, Criteria1:=docnum
 .AutoFilter Field:=6, Criteria1:=docdt
 .AutoFilter Field:=8, Criteria1:=posamt * -1  
Range("H1").Select
Selection.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
visibletotal = Application.WorksheetFunction.Sum(Selection.SpecialCells(xlCellTypeVisible))
If visibletotal = -1 * posamt Then   
Selection.Offset(0, 5).Value = "matched CN"
c.Offset(0, 3).Value = "matched CN" 
Else
c.Offset(0, 3).Value = "UNmatched CN"
Selection.Offset(0, 5).Value = "UNmatched CN"
End If
End With
Next c
kamal_hamad
  • 69
  • 6
  • First remove `On Error Resume Next` this line hides all error messages you get but the errors still occur. If you cannot see the errors you cannot fix them, if you don't fix them your code cannot work. Fix your errors first. Then read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and get rid of all `.Select` statements with that technique. Also have a look at [Sum visible (or filtered) range in an Excel Spreadsheet](https://stackoverflow.com/questions/17835292/sum-visible-or-filtered-range-in-an-excel-spreadsheet). – Pᴇʜ Sep 28 '21 at 08:18
  • Also have a look at [Easiest way to loop through a filtered list with VBA?](https://stackoverflow.com/questions/10849177/easiest-way-to-loop-through-a-filtered-list-with-vba) this might help too. – Pᴇʜ Sep 28 '21 at 08:21

0 Answers0