0

I'm trying to use a loop variable as autofilter criteria. The loop is working fine when I put the autofilter criteria manually. When I try to integrate my variable (i), it is not working anymore.

Column A contains a series of material numbers ranging from 1-115. One by one I need to filter them and calculate the MIN, MAX, AVERAGE,... on column C values. The range for the formulas is the number of rows filtered. This part is working fine.
As I don't want to repeat this code 115 times, I want to use a loop. The loop itself is working fine. The autofilter should use the value for I (loop counter) in the selection criteria. This is not working.

Dim rijen As Integer
Dim start As Integer
Dim filterA As String
Dim filterB As String
Dim aantal As Integer
Dim i As Integer

range("A2").Select
Selection.End(xlDown).Select
aantal = ActiveCell.Value
    
i = 1
Do Until i > aantal
    
    
range("A2").Select

' THIS IS THE PART THAT IS NOT WORKING

ActiveSheet.range("$A$2:$H$1045876").AutoFilter Field:=1, Criteria1:=" & i & "

'THIS IS WORKING FINE AGAIN

ActiveSheet.AutoFilter.range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, A).Select
start = ActiveCell.Row

range("A2").Select
Selection.Offset(1, 0).Select
rijen = range(Selection, Selection.End(xlDown)).Rows.Count + start - 1
filterA = ("C" & start)
filterB = ("C" & rijen)

range("D2").Select
Selection.Offset(1, 0).Select
ActiveCell.Formula = "=MIN(" & filterA & ":" & filterB & ")"
 range("E2").Select
Selection.Offset(1, 0).Select
ActiveCell.Formula = "=MAX(" & filterA & ":" & filterB & ")"
 range("F2").Select
Selection.Offset(1, 0).Select
ActiveCell.Formula = "=GEMIDDELDE(" & filterA & ":" & filterB & ")"
 range("G2").Select
Selection.Offset(1, 0).Select
ActiveCell.Formula = "=STDEVA(" & filterA & ":" & filterB & ")"
 range("H2").Select
Selection.Offset(1, 0).Select
ActiveCell.Formula = "=MIN(RC[-3],(RC[-2]+RC[-1]))"

ActiveSheet.range("$A$2:$H$1045876").AutoFilter Field:=1

i = i + 1
Richard Wilson
  • 297
  • 4
  • 17
EVE
  • 1

1 Answers1

0
Criteria1:=" & i & "

This is wrapped in quotes, so it is taking it as a literal string value of "& i &". Just remove the quotes and ampersands.

Criteria1:= i

You are using .Select way too much. This question should be of use to you.

Any particular reason you are using VBA to insert all of these formulas rather than, say, an Excel table?

Jody Highroller
  • 999
  • 5
  • 12
  • Thx! this is working! I'm self taught so still learning to optimise my codes. I usually record and then 'clean' the code for variable ranges and selections. The data set I use are purchase orders so the set will vary each time I run it. This time it is only 115 materials, but the other set contains over 1000 materials and 40k rows. I only want the first row of each material to contain the formuas. No idea on how to do this in an Excel table :-) – EVE Sep 22 '21 at 07:11