Relative Referencing Formulas in Arrays

[bbcdancer]

Are absolute and relative referencing doable in VBA arrays?

Is it possible to put formulas in VBA arrays and make the cells
absolute and relative in the formulas.
_________________
For example: Need to $e4 to cell to be relative, so it will change row
number each time and $F$1 to be absolute. This is in relation to the
code below. Absolute values is okay. However, relative values, I've
tried inserting rc[5] for the relative array value, but this does not
work for the Sub ProcData() macro (see below)


The Array.......
v(0) = Array("Monthly", "=(Text(e4,""dd"")&TEXT($F$1,""-mm-yy""))", 10)



_______________________


Sub ProcData()
Dim v As Variant
Dim v1 As Variant
Dim rng As Range
Dim cell As Range
Dim res As Variant
v1 = Array("M", "S", "Q", "D", "Y")
ReDim v(0 To 4)
v(0) = Array("Monthly", "=(Text(e4,""dd"")&TEXT($F$1,""-mm-yy""))", 10)

v(1) = Array("Semi-Ann", "'3", 30)
v(2) = Array("Quarterly", "'DDE", 25)
v(3) = Array("Decade", "'10y", 4)
v(4) = Array("Yearly", "'123", 5)
Set rng = Range(Cells(3, 4), Cells(3, 4).End(xlDown))
For Each cell In rng
res = Application.Match(cell.Value, v1, 0)
If Not IsError(res) Then
cell.Offset(0, 3).Resize(1, 3).Value = v(res - 1)
End If
Next
End Sub


__________________________
Thanks for any help given.


Brenda

 

[Bob Phillips]

Not really sure what you are trying to do, put the formula in the cell, or
the value, but assuming formula, you should use a variable, and test the
matched value and do an If ... Else..