searching col / array for value greater than (less than) reference value

**LeonW** · 02-11-2014, 08:53 PM

Have not played with VBS in a long time so I'm a bit rusty.

To properly describe the problem here's sample data:
c d e f g
15.6 16 15.48 15.58 15064800
15.84 16.17 15.29 16.03 19420500
16.39 16.53 15 16.14 18822900
15.99 16.02 15.2 15.26 11079900
15.28 15.53 15 15.06 9578000
15.12 15.34 14.71 14.89 8335100
15.01 15.06 14.79 14.93 9626900
14.54 15 14.5 14.96 9536100
14.86 15.08 14.15 14.17 20721400
14.38 14.93 14.13 14.87 13541100
14.56 15.06 14.52 14.8 10398400
14.89 14.99 14.16 14.17 9668000
14.37 14.63 13.62 13.66 13810900
13.33 13.58 12.49 12.97 21110700
13.14 13.23 12.26 12.28 19419100
11.97 12.72 11.5 11.53 25008900
11.6 11.87 10.98 11.01 17938500
10.31 11.25 10 11.08 32782400
11.62 11.9 11.13 11.74 16953300
12.08 12.23 11.17 11.3 17192200
11 11.06 10.1 10.12 15280900
10.08 10.64 9.69 10.56 20180200
10.29 10.71 10.07 10.45 14347000
10.56 11.14 10.5 11.13 12694500
10.56 11.05 10.5 10.51 10286900
10.24 10.49 9.76 9.99 14840600
10.03 10.3 9.75 10.26 17700100
9.16 10.07 9.16 9.68 13519000
9.3 10 9.3 9.59 10134200
9.8 10.9 9.5 10.86 15730700
10.85 11.81 10.65 11.33 20073100
11.85 12.7 11.46 12.62 19678100
12.52 13.18 12.29 13.13 18683300

The macro setup starts:

Please Login or Register  to view this content.

I am trying to find in column C the first value less than 90% of C1. None of Vlookup, Index/Match, Find get me there. I am trying to avoid the inefficiency of loops as the data is massive.
I'll also be looking for 120% and various other similar items in other columns.

Suggestions towards solving the puzzle would be much appreciated. If I have no other choice I'll loop

Thank you,
LeonW

**martindwilson** · 02-11-2014, 09:36 PM

well the formula would be
=INDEX($C$1:$C$33,MATCH(TRUE,INDEX($C$1:$C$33<C1*0.9,0),0))

**LeonW** · 02-12-2014, 02:47 PM

Originally Posted by martindwilson

well the formula would be
=INDEX($C$1:$C$33,MATCH(TRUE,INDEX($C$1:$C$33<C1*0.9,0),0))

Hello Martin,
Thanks for the quick reply. I did have some adventures as the "formula" has to deal with varying size data sets and had to be "adjusted" for VBA. I'll "play" with the formula to get my other info. Hopefully I won't get into too much trouble

. Pointing out easy opportunities to tighten up / improve the code would be great.

Here's a sample of what I have:

Sub M10Pct()
ActiveSheet.Name = Range("A1").Text & Range("B1").Text
' Initialize variables:
'r = Range String
'Lrow = last row number with data
'M10row = row of M10 value
'M10val = M10 value
'IdxStr = String variable to build up INDEX/MATCH formula
Dim r As String, Lrow As Long, IdxStr As String
Lrow = Cells(Rows.Count, "C").End(xlUp).Row
r = "$C1:$C" & CStr(Lrow)
'Set rw = Range("C2:C" & Lrow)
'rw.Select
' Assemble INDEX/MATCH string for M10 (90% of Purchase)
' =INDEX($C$1:$C$Lrow,MATCH(TRUE,INDEX($C$1:$C$Lrow<C1*0.9,0),0))
' Note: using more than one "&" in a statement yields syntax error
IdxStr = "=INDEX(" & r
IdxStr = IdxStr & ",MATCH(TRUE,INDEX("
IdxStr = IdxStr & r
IdxStr = IdxStr & "<C1*0.9,0),0))"
M10row = Evaluate(IdxStr).Row
M10val = Evaluate(IdxStr)
End Sub

Thank you!