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

#### irekevin

##### New Member
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"

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
End If

Next i

Application.ScreenUpdating = True  'Turn screen updating ON

End Sub``````

Last edited:

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### JoeMo

##### MrExcel MVP
Untested, but see if this works:

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

#### irekevin

##### New Member
No luck, same error, same location.

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

#### Norie

##### Well-known Member
What's the value of the variable ref?

#### irekevin

##### New Member
What's the value of the variable ref?

I put in the original post.

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

#### Norie

##### Well-known Member
ref is always 'Deck Elements'!\$I\$21:\$I\$90?

#### irekevin

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

#### Norie

##### Well-known Member
When you get the error what's its value?

#### irekevin

##### New Member
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

##### Well-known Member
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.

Replies
13
Views
3K
Replies
2
Views
925
Replies
7
Views
466
Replies
5
Views
496
Replies
12
Views
8K

1,195,628
Messages
6,010,772
Members
441,568
Latest member
abbyabby

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back