Loop Through Array And Perform Formula

trying to loop through an array, with each loop calculating a formula (VLOOKUP) for one cell based on the values of a cell in another sheet.

Not sure but I think it has something to do with looking up a text value while the loop returns a value.


Private Sub ExpandFormula()
'calculates Counttarget formula in COUNTTARGETS sheet across required range
Dim CellsDown As Long, CellsAcross As Integer
Dim i As Long, j As Integer
Dim TempArray() As String
Dim TheRange As Range
Dim CurrentValue As String

'get the dimensions
CellsDown = Worksheets("SEPARATE").Range("D9").Value
CellsAcross = Worksheets("SEPARATE").Range("E9").Value

'redimension temporary array
Redim TempArray(1 To CellsDown, 1 To CellsAcross) .....................