hi there.
I've got a table (well, not really...a series of data sets on one sheet) that I need to use.
I'm writing a macro/function to find the right dataset and then return the range of one of the columns under it.
Each data set has 2 columns, a title above it and an undetermined number of lines like this:
In a normal worksheet, I could do an index lookup in one of these sets using
- match to find the right title in the first row
- offset to return ranges for each of the two desired columns
- match to find the desired row in the first column
- index to return the corresponding row of the second column
Here's an example I threw together using the above data set. I find the "full" set, then find the 2nd column entry corresponding with 0.30 (ignore that I linked directly to this number...normally that would come from another cell).
How would I do this in vba? I'm not seeing functions like "offset" in the application.worksheetfunction object browser. I'm planning on putting these worksheets full of data into an add-in so I also am going to be using "indirect", which I don't see either. Here's what it looks like with the indirect references...and is what I'm trying to duplicate in VBA.
<*phew*>...anyway, to avoid having to type this more than once, I've decided to put the reference tables and the functions to hunt through them into an add-in so I just need to use a function like "chartref(sheetname,tablename,value)" to get what the corresponding value back out.
Summary: indirect and offset in vba??????
I've got a table (well, not really...a series of data sets on one sheet) that I need to use.
I'm writing a macro/function to find the right dataset and then return the range of one of the columns under it.
Each data set has 2 columns, a title above it and an undetermined number of lines like this:
ChartRef.xls | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | full | 2-a | 1-a | ||||||||
2 | |||||||||||
3 | 0.00 | 9.05 | 0.00 | 9.05 | 0.00 | 7.07 | |||||
4 | 0.05 | 9.06 | 0.05 | 9.06 | 0.06 | 7.11 | |||||
5 | 0.10 | 9.13 | 0.10 | 9.13 | 0.12 | 7.19 | |||||
6 | 0.14 | 9.14 | 0.14 | 9.14 | 0.21 | 7.40 | |||||
7 | 0.18 | 9.21 | 0.18 | 9.21 | 0.24 | 7.50 | |||||
8 | 0.23 | 9.35 | 0.23 | 9.35 | 0.29 | 7.70 | |||||
9 | 0.27 | 9.42 | 0.27 | 9.45 | 0.31 | 7.84 | |||||
10 | 0.30 | 9.55 | 0.30 | 9.55 | 0.36 | 8.04 | |||||
11 | 0.32 | 9.62 | 0.32 | 9.62 | 0.41 | 8.18 | |||||
12 | 0.33 | 9.69 | 0.34 | 9.63 | 0.48 | 8.39 | |||||
13 | 0.36 | 9.79 | 0.36 | 9.70 | 0.53 | 8.59 | |||||
14 | 0.37 | 9.90 | 0.38 | 9.76 | 0.56 | 8.70 | |||||
15 | 0.40 | 9.93 | 0.41 | 9.80 | 0.59 | 8.87 | |||||
16 | 0.43 | 10.03 | 0.43 | 9.87 | 0.61 | 9.00 | |||||
17 | 0.45 | 10.14 | 0.46 | 9.94 | 0.63 | 9.14 | |||||
18 | 0.49 | 10.37 | 0.51 | 10.08 | 0.66 | 9.27 | |||||
19 | 0.53 | 10.68 | 0.54 | 10.18 | 0.68 | 9.41 | |||||
20 | 0.57 | 10.98 | 0.56 | 10.28 | 0.72 | 9.54 | |||||
21 | 0.60 | 11.28 | 0.59 | 10.45 | 0.76 | 9.71 | |||||
22 | 0.63 | 11.42 | 0.63 | 10.62 | 0.79 | 9.82 | |||||
23 | 0.67 | 11.59 | 0.65 | 10.76 | 0.82 | 9.99 | |||||
24 | 0.70 | 11.79 | 0.68 | 10.93 | 0.85 | 10.12 | |||||
25 | 0.73 | 12.02 | 0.72 | 11.20 | 0.88 | 10.26 | |||||
26 | 0.76 | 12.19 | 0.74 | 11.33 | 0.91 | 10.43 | |||||
27 | 0.80 | 12.53 | 0.76 | 11.53 | 0.93 | 10.56 | |||||
28 | 0.84 | 12.86 | 0.78 | 11.60 | 0.97 | 10.83 | |||||
29 | 0.88 | 13.27 | 0.81 | 11.67 | 1.00 | 10.97 | |||||
30 | 0.91 | 13.60 | 0.83 | 11.77 | |||||||
31 | 0.95 | 14.10 | 0.87 | 11.91 | |||||||
32 | 0.98 | 14.57 | 0.92 | 12.15 | |||||||
33 | 1.00 | 14.87 | 0.95 | 12.28 | |||||||
34 | 0.98 | 12.45 | |||||||||
35 | 1.00 | 12.59 | |||||||||
36 | |||||||||||
Panel_Buckling |
In a normal worksheet, I could do an index lookup in one of these sets using
- match to find the right title in the first row
- offset to return ranges for each of the two desired columns
- match to find the desired row in the first column
- index to return the corresponding row of the second column
Here's an example I threw together using the above data set. I find the "full" set, then find the 2nd column entry corresponding with 0.30 (ignore that I linked directly to this number...normally that would come from another cell).
Code:
=INDEX(OFFSET(A1,2,MATCH("full",1:1,0),100,1),MATCH(B10,OFFSET(A1,2,MATCH("full",1:1,0)-1,100,1),0))
How would I do this in vba? I'm not seeing functions like "offset" in the application.worksheetfunction object browser. I'm planning on putting these worksheets full of data into an add-in so I also am going to be using "indirect", which I don't see either. Here's what it looks like with the indirect references...and is what I'm trying to duplicate in VBA.
Code:
=INDEX(OFFSET(INDIRECT("'[ChartRef.xla]"&G25&"'!$A$1"),2,MATCH(I25,INDIRECT("'[ChartRef.xla]"&G25&"'!$1:$1"),0),100,1),MATCH(J25,OFFSET(INDIRECT("'[ChartRef.xla]"&G25&"'!$A$1"),2,MATCH(I25,INDIRECT("'[ChartRef.xla]"&G25&"'!$1:$1"),0)-1,100,1),0))
Summary: indirect and offset in vba??????