Relative Referencing Formulas in Arrays
 

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

Relative Referencing Formulas in Arrays
 

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

--
HTH

Bob Phillips

wrote in message
oups.com...
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