VBA - Error

D

dipsy

sheets("Approval-Summary_%").Select
Range("J53:K53").Select
ActiveCell.Formula = "=HighLevelSummary_%!$C$5"

When I run this code, I get Run Time Error - 1004
Application defined or object defined error.

I will appreciate assistance in identifying the problem
with the code.

Approval-Summary_%
HighLevelSummary_%!$C$5
- are the 2 sheets. In cells - J53-K53 I want to put the
link to cell from sheet HighLevelSummary cell C5.
 
D

Don Guillett

try this. Modify to suit.
'Set frng = Range("h8:h" & Range("a65536").End(xlUp).Row)
set frng=range("j53:k53")
With frng
.Formula = "=HighLevelSummary_%!$C$5"
' .Formula = .Value'changes to values
End With
 
D

dipsy

This did not work. What worked was when I changed the
sheet name to "HighLevelSummary_Per"

Do u know why that is?
 
D

Dave Peterson

Try this manually:

Rename the sheet back to highlevelsummary_%
In an empty cell (not on highlevelsummary_%, type an equal sign (=) and use the
mouse to point back a cell on that highlevelsummary_% worksheet.

You'll see a formula like:
='highlevelsummary_%'!A3

Notice the single quotes. Excel wants them under certain circumstances.
(spaces in the worksheet name and special characters in the name (% and $ both
caused the single quotes).)

So you could change Don's line of code:
from:
..Formula = "=HighLevelSummary_%!$C$5"
to:
..Formula = "='HighLevelSummary_%'!$C$5"

In fact, excel will never get mad if you put them in (even if you don't actually
need them). It'll just strip them out when it sees it doesn't need them.
 
Top