EVALUATE

[footoo]

Is there a way to use EVALUATE to enter the results of the formula directly into the range instead of doing it the following way?

Sub v()
Dim r As Range: Set rng = [C59:C73].Address
rng.FormulaR1C1 = "=IFERROR(INDEX(INDIRECT(""'""&RC2&""'!N:N""), MATCH(9.99999999999999E+307, INDIRECT(""'""&RC2&""'!N:N""), 1)), """")"
rng.Value = rng.Value
End Sub

 

[DanteAmor]

A couple of observations in your macro, you declare the variable r as Range, but you don't use it in your macro.
This statement: Set rng = [C59:C73].Address marks an error for me. This is not how it should be, it should be: Set r = [C59:C73]

I tried a few ways to put the Evaluate statement, but it didn't work with a range, so it works for me in a loop:

Sub v3()
  Dim r As Range
  For Each r In Range("C59:C73")
    r.Value = Evaluate("=IFERROR(INDEX(" & r.Offset(, -1) & "!N:N,MATCH(9.99E+307," & r.Offset(, -1) & "!N:N, 1)),"""")")
  Next
End Sub

 

[footoo]

A couple of observations in your macro, you declare the variable r as Range, but you don't use it in your macro.
This statement: Set rng = [C59:C73].Address marks an error for me. This is not how it should be, it should be: Set r = [C59:C73]

I tried a few ways to put the Evaluate statement, but it didn't work with a range, so it works for me in a loop:

Sub v3()
  Dim r As Range
  For Each r In Range("C59:C73")
    r.Value = Evaluate("=IFERROR(INDEX(" & r.Offset(, -1) & "!N:N,MATCH(9.99E+307," & r.Offset(, -1) & "!N:N, 1)),"""")")
  Next
End Sub

Oops. Also, the formula makes no sense.
Thanks for your input but I want to avoid looping.
The following achieves this but I was hoping to write the results directly to the range (instead of writing a formula then converting to value).

Sub v()
Dim rng As Range: Set rng = [C59:C73]
rng.Offset(0, 1).FormulaR1C1 = "=LOOKUP(9.99999999999999E+307,INDIRECT(""'"" & RC2 & ""'!N:N""))"
rng.Value = rng.Value
End Sub

 

[footoo]

I could do per below, but would still like to know if it is possible to write the results directly to the range by using EVALUATE.
Or perhaps there is a formula that does not use INDIRECT?

Sub UpdateValuesWithArray2()
Dim rng As Range, dataArray, i&
Set rng = [C59:C73]
dataArray = rng
For i = 1 To UBound(dataArray, 1)
    dataArray(i, 1) = Application.WorksheetFunction.Lookup _
    (9.99999999999999E+307, Evaluate("'" & rng(i)(1, 0) & "'!N:N"))
Next i
rng = dataArray
End Sub

 

[DanteAmor]

If you want a formula that doesn't use INDIRECT, but want to take the sheet name of a cell, it's not possible. But with the macro it could be:

Sub v3a()
  Dim r As Range
  For Each r In Range("C59:C73")
    r.Value = Evaluate("=LOOKUP(9.99E+307,'" & r.Offset(, -1).Value & "'!N:N)")
  Next
End Sub

I tried to use EVALUATE directly, but when you use it on a range it doesn't work for this case.

 

[footoo]

If you want a formula that doesn't use INDIRECT, but want to take the sheet name of a cell, it's not possible. But with the macro it could be:

Sub v3a()
  Dim r As Range
  For Each r In Range("C59:C73")
    r.Value = Evaluate("=LOOKUP(9.99E+307,'" & r.Offset(, -1).Value & "'!N:N)")
  Next
End Sub

I tried to use EVALUATE directly, but when you use it on a range it doesn't work for this case.

Yes, that was the same as I experienced.
I think I'll stick with the macro in the original post (corrected, of course).