Run-time error '-2147417848(80010108)' Method Value of object range failed

[beedistinct]

Sub CnvToHyper()
'
' Macro1 Macro that converts a display to Hyperlink enable so that
' Mouse on Hover works.
'
 Dim display As String
 Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual
 Sheets("TestHover").Activate
 With ActiveSheet
    For i = 1 To 0 Step -1
       For j = 6 To 0 Step -1
          [FirstDisp].Offset(i, j).Select
          display = ActiveCell.Value
          [COLOR=#ff0000][B]ActiveCell.Value = "=IFERROR(HYPERLINK(MouseOver(""" & display & """)),""" & display & """)"[/B][/COLOR]
       Next j
    Next i
 End With
 Application.ScreenUpdating = True
 Application.Calculation = xlCalculationAutomatic
End Sub

It is giving the error on the statement in red.
I'm trying to convert a data that is 2 rows 6 columns wide such that each cell has a formula as listed in red above. Am using Excel 2013 with no add-ins and am about to go nuts. What am I doing wrong here??!! Please help. Each cell in the data contain an alpanumeric ID 12 chars long and nothing else.

Here is the MouseOver function

Public Function MouseOver(PassedValue As String)
    If [ErrBox] = PassedValue Then Exit Function
    [ErrBox] = PassedValue
End Function

 

[sergioMabres]

Why do not try with
ActiveCell.Formula = "=IFERROR...
Sergio

 

[Norie]

I can run that code without error, and it does insert the correct formulas, though obviously my data is completely different from what you have.

Could you upload a sample workbook to somewhere like Box.net and then post a link to it here?

 

[beedistinct]

Why do not try with
ActiveCell.Formula = "=IFERROR...
Sergio

Yes. Prior to using .value I was using .FormulaR1C1 and .Formula. They gave me similar run-time errors.

 

[beedistinct]

I can run that code without error, and it does insert the correct formulas, though obviously my data is completely different from what you have.

Could you upload a sample workbook to somewhere like Box.net and then post a link to it here?

Here is the link. Thanks for your help.

https://www.box.com/s/1qi17pv8klyw4grid6n2

 

[Norie]

I've tried various things but can't seem to find what the problem is.

I do have an inkling that it might be something to do with the MouseHover UDF and/or circular references.

UDFs are normally used to return a value as a result to the cell they are in, not setting value in other cells.

If I change the UDF to this everything works without error though I doubt very much it does what you want.

Public Function MouseOver(PassedValue As String)
  MouseOver = PassedValue
End Function

Actually, what is it you are trying to do?

 

[beedistinct]

I've tried various things but can't seem to find what the problem is.

I do have an inkling that it might be something to do with the MouseHover UDF and/or circular references.

UDFs are normally used to return a value as a result to the cell they are in, not setting value in other cells.

If I change the UDF to this everything works without error though I doubt very much it does what you want.

Public Function MouseOver(PassedValue As String)
  MouseOver = PassedValue
End Function

Actually, what is it you are trying to do?

Thaks for your help. Were you able to reploicate the problem/run-time error?

I'm trying to do a Vlookup on the cells when someone hovers a mouse over it. So, basically, I have a bunch of cells with some values in it. I want the user to see those same values but when he moves the mouse over the cell then it uses the value that is displayed in the cell to do a simple vlookup.
The strange part is that if I manually type the HYPERLINK formula pasted above in all my cells where I want the hover to work, it works fine! It is just that when I'm using VBA to introduce the formulae in those same cells, I'm getting the problem.