I've tried for 2 hours... coming up with nothing. I really appreciate any help you can give, I've tried to give this some serious thought before posting, but have come up short.

What I'm trying to do is this...

Write a formula to a cell in Sheet 1, using text in a cell in Sheet 2.

Error on this line. It won't let me use the 'ref' variable inside.

Cells(2 + i, 27).Formula = "=IFERROR(MATCH(" + Cells(2 + i, 26).Address + "," + ref + ",1),1)"

Where ref = 'Deck Elements'!$I$21:$I$90

Sheet 1 = where the code is written

Sheet 2 = "References"

What I'm trying to do is this...

Write a formula to a cell in Sheet 1, using text in a cell in Sheet 2.

Error on this line. It won't let me use the 'ref' variable inside.

Cells(2 + i, 27).Formula = "=IFERROR(MATCH(" + Cells(2 + i, 26).Address + "," + ref + ",1),1)"

Where ref = 'Deck Elements'!$I$21:$I$90

Sheet 1 = where the code is written

Sheet 2 = "References"

Code:

```
Sub VAHI()
Application.ScreenUpdating = False 'Turn screen updating OFF
'Determine total # of rows
n = 0
Do
n = n + 1
Loop Until IsEmpty(Cells((2 + n), 1))
'Create RESULTS TABLE
For i = 0 To 20 'Row step
Dim arr, val, pos, ref
arr = ThisWorkbook.Sheets("References").Range("A3:A100") 'Set range of Elements
val = Cells(2 + i, 11).Value 'Elemkey
pos = Application.Match(val, arr, False) 'Call Match function to return row# of elemkey in Reference tab
ref = ThisWorkbook.Sheets("References").Cells(2 + pos, 4).Value
If Not IsError(pos) Then
' MsgBox val & " is at position " & pos
Cells(2 + i, 27).Formula = "=IFERROR(MATCH(" + Cells(2 + i, 26).Address + "," + ref + ",1),1)"
'=IFERROR(MATCH(Z2,'Deck Elements'!$I$21:$I$90,1),1) -- Sample Formula
Else
MsgBox val & " not found!"
End If
Next i
Application.ScreenUpdating = True 'Turn screen updating ON
End Sub
```

Last edited: