offset with vba

[kkjensen]

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:

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).

=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.

=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))

<*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??????

 

[lenze]

Offset in VBA is a property of a Range. So something like

Range("$A$1").Offset(0,1)

to identify B1

lenze

 

[kkjensen]

Thanks for the reply

Can that be combined with "indirect"? In my last equation there I build the reference for a cell [addinname.xla]sheetnamewhateveritmaybe!$A$1 and then offset it.

I've only seen offset when I've recorded things but it generally looked like:

Activecell.Offset(0,1).Range("A1").Select

where yours is range then offset...in order to get a range that is off to one side, would it look like

Range("$A$1").Offset(0,1).Range("A1:A100")

??? I'm trying to return a range that can be used with index.