Run Time Error 1004 'Application Defined or Object Defined Error'

[irekevin]

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"

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

 

[JoeMo]

Untested, but see if this works:

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

 

[irekevin]

No luck, same error, same location.

I did replace all my "+" with "&" like you had, I think that's right.

 

[Norie]

What's the value of the variable ref?

 

[irekevin]

ADVERTISEMENT

What's the value of the variable ref?

I put in the original post.

As text, Ref = 'Deck Elements'!$I$21:$I$90

 

[Norie]

ref is always 'Deck Elements'!$I$21:$I$90?

 

[irekevin]

ADVERTISEMENT

No, but similar. It is part of an array that I search to pull a range location.

 

[Norie]

When you get the error what's its value?

 

[irekevin]

Almost exactly what it should be

On error: Deck Elements'!$I$21:$I$90
Actual: 'Deck Elements'!$I$21:$I$90

Just missing the apostrophe at the start.

 

[Norie]

The missing apostrophe is the problem.

In a formula if you refer to a sheet with a space in the name then the name needs to be enclosed in single quotes.