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