Nearest one or two or three numbers in a row of data
Hi
This is my take on what I have, others may have something quicker and
cleaner.
Sub FindSum()
Dim mySht As Worksheet
Dim myCrng As Range, myNum As Range
Dim c As Range
Set mySht = Worksheets("Sheet1")
Set myCrng = mySht.Range("B2:I10")
Set myNum = mySht.Range("A1")
For Each c In myCrng
If Not c < myNum Then
With c
.Cells.ClearContents
End With
End If
Next c
End Sub
Change the ranges to suit.
The above range is assumed as Columns("B:I").
The answer Cells are assumed in Column("A") and relative to each row of
numbers and would be e.g "=Sum("A2:I2")"
Cell A1 can be set to any number of your choosing which gives you more
flexibility than using a definitive value.
Essentially, this will delete any number that is not less than the
number you set in Cell A1, which in your example was 50 and returned a
total of 142.
If the numbers that exceed your limit are not important then, deleting
them won't be too stressful, conversely you may want to wait until
someone else comes up with a better solution.
HTH
Mick.
|