A userfrom button prompts the user to enter a value in a textbox. The code SHOULD find all values or a single value that meets the the autofilter criteria:
highlighted area is just the Named Range BALANCES2020 which changes from week to week(becomes smaller by hiding top row. This Named Range is where autofilter is performed and x or a set of x values is determined.
The lowest balance in column 11 is $126.04. When only ONE value from autofilter is found, the result is incorrect as below
This value of $1,685.41 is the very first row of the sheet and is outside the Named Range. This only happens
when the autofilter code finds only ONE value. If it finds multiple values, the code works correctly and all values found are LESS than x, the value to use to find all values less than. I hope I've explained this clearly. It's the code it gives an incorrect result way outside the Named Range when only one value is found.
Can someone help with why it's not performing correctly with a SINGLE autofilter result, but works fine when yielding multiple results all WITHIN the boundaries of the Named Range. Sorry for being long winded. Thanks for anyone's help cr ?
Code:
Dim x As Long
x = BALS2020.ComboBox1.value ' x = selected value from BALS2020 userform
Sheets("CREDITCDBALS20").Activate 'CREDITCDBALS20 = sheet Named Range BALANCES2020 is on
With Range("BALANCES2020")
.Offset(-1, 0).Resize(.Rows.count + 1).AutoFilter 11, "<=" & x
.SpecialCells(xlVisible).Copy Sheets("REPORT").Range("A1")
.AutoFilter
End With
LOWBALS20.Show 'LOWBALS20 = userform to display autofilter results
Unload BALS2020 'close form
Unload ALLMACROS 'close form
Sheets("BUDGET").Select 'display main formhighlighted area is just the Named Range BALANCES2020 which changes from week to week(becomes smaller by hiding top row. This Named Range is where autofilter is performed and x or a set of x values is determined.
The lowest balance in column 11 is $126.04. When only ONE value from autofilter is found, the result is incorrect as below
This value of $1,685.41 is the very first row of the sheet and is outside the Named Range. This only happens
when the autofilter code finds only ONE value. If it finds multiple values, the code works correctly and all values found are LESS than x, the value to use to find all values less than. I hope I've explained this clearly. It's the code it gives an incorrect result way outside the Named Range when only one value is found.
Can someone help with why it's not performing correctly with a SINGLE autofilter result, but works fine when yielding multiple results all WITHIN the boundaries of the Named Range. Sorry for being long winded. Thanks for anyone's help cr ?
