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

beedistinct

Board Regular
Joined
Jun 24, 2011
Messages
62
Code:
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
Code:
Public Function MouseOver(PassedValue As String)
    If [ErrBox] = PassedValue Then Exit Function
    [ErrBox] = PassedValue
End Function
 
Last edited:

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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?
 
Upvote 0
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.
Code:
Public Function MouseOver(PassedValue As String)
  MouseOver = PassedValue
End Function

Actually, what is it you are trying to do?
 
Upvote 0
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.
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,222,018
Messages
6,163,428
Members
451,836
Latest member
boxboxbox

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top