Variables in an Index, MATCH FORMULA

SGBCleve

New Member
Joined
Dec 4, 2011
Messages
35
I am trying to insert variables into an Index formula, but I am a bit confused about how to put range names in. The range is varying by offset and I know I can name one range. I am not sure if the offset works with a named range or not. The array I am indexing is a range called rTime. The value in MATCH i rMxTA, and the reference array is AbsTA.

My (abbreviated) code is

Dim Wb As Workbook
Dim rMxTA As Range

Dim i As Integer
Dim j As Integer
Dim tTA As Range

Dim rTime As Range
Dim Count As Integer
Dim n As Integer
Dim AbsTA As Range
Dim rTime as Range


The Dim statements are omitted for brevity


Set Wb = Workbooks("Ext Time 1_7_18.xlsm")
Count = Wb.Sheets.Count

For i = 2 To Count
With Wb.Sheets(i)
n = .Range("P104", .Range("P104").End(xlDown)).Count
For j = 1 To n
Set rMxTA = .Range("P104").Offset(j - 1, 0)

Set tTA = .Range("P226").Offset(j - 1, 0)

Set rTime = .Range("O56:O101")
Set AbsTA = .Range("P56:P101").Offset(0, j - 1)

tTA.Formula = "=Index(rTime,MATCH(tTA,AbsTA,0)"

Continues with Next j, End With, Next i, etc

Do I need to name rTime, tTA and AbsTA as range names? I suppose I could do this a R1C1 with variables in the cells as another option, but it would be easier to just pass the ranges into the formula, if possible.

Thanks for taking the time to look
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

SGBCleve

New Member
Joined
Dec 4, 2011
Messages
35
I should add that I can name the rTime and AbsTA ranges so I constructed the formula like this:tTA.FormulaR1C1 = "=Index(Time,MATCH(R[-122]C,ABSTA,0))" where "Time" and "ABSTA" are named ranges, but I need to offset ABSTA
I am just wondering if there is a way to write a variable in this kind of function without naming the range. Perhaps, with Application. WorksheetFunction??
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,925
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top