Hyperlink Syntax Issue

**sgrande01** · 07-10-2013, 01:48 PM

All,

I've been trying for a very long time to get a dynamic hyperlinking formula in vba to work correctly. I was having issues with the simple =Hyperlink() function but found a solution to have the formula work correctly. i have some ranges i need to use within the formula but i can't get the syntax. i've been working on and off on this for 3 days with no success. can anyone take a look:

Please Login or Register  to view this content.

what i would like to appear in excel:

=HYPERLINK("#'OPRATIONS1'!BK9","OPRATIONS1")

the range 06 would be "OPRATIONS1", the name of the tab.

Thank you

edit: i am getting an "Application-defined or object defined error" message.

**Lifesigns** · 07-10-2013, 01:54 PM

Hey welcome to the forum and thanks for using the code blocks.

Do you have a sample file we could take a look at?

**sgrande01** · 07-10-2013, 02:01 PM

i'm sorry - neither IE or chrome will allow me to open the attachment window.

**ussenterprise** · 07-10-2013, 02:04 PM

You may have popups blocked. Can these be unblocked?

**sgrande01** · 07-10-2013, 02:09 PM

the pop-up is appearing but there is nothing within the window.

i can email the file?

**ussenterprise** · 07-10-2013, 02:12 PM

Unfortunately, I cannot accept emails. A few other users here may be able to assist though with a link to something like a dropbox...

[edit] And its rather odd that there is nothing in the popup!

**sgrande01** · 07-10-2013, 02:25 PM

any way you think you can try without having the file?

**ussenterprise** · 07-10-2013, 03:37 PM

I'll give it a go:

Please Login or Register  to view this content.

This somewhat similar to what I am doing. I moved the first '&' outside of the parentheses. Moved around some quotes.
One thing you could try is:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

This will help in troubleshooting...

**ussenterprise** · 07-10-2013, 03:42 PM

Hm. Can't get the MsgBox to run. It might be due to not having all the information. Still working it out...

**ussenterprise** · 07-10-2013, 03:45 PM

I am getting a Method 'Range' of object '_Global' failed (1004 error)...

**ussenterprise** · 07-10-2013, 04:01 PM

Can you paste the whole macro in?

**ussenterprise** · 07-10-2013, 04:10 PM

I ran this:

Please Login or Register  to view this content.

I am only returning an 'I' in the box. What is currentRow2? I suspect this is a Dim, but want to be sure...

**ussenterprise** · 07-10-2013, 04:25 PM

If I MsgBox this:

Please Login or Register  to view this content.

I get the Range of object failed.

I removed both ranges and reattempted:

Please Login or Register  to view this content.

I get expected: end of statement.

It looks like it doesn't like the '(' to be inside the quote...

Still looking...

**ussenterprise** · 07-10-2013, 04:32 PM

Please Login or Register  to view this content.

works normally.

Please Login or Register  to view this content.

works normally (Removed triple quotes here...)

Please Login or Register  to view this content.

works normally (removed triple quotes again...)

Please Login or Register  to view this content.

works normally. I added the & at the end.

Try this:

Please Login or Register  to view this content.

**sgrande01** · 07-10-2013, 04:33 PM

sorry for the delayed response - in some meetings.

i've went through it so, so slowly and continue to get an end of statement or application defind or object defined error. here is the entire code:

also, this DID work, meaning it pasted into excel, however, the hyperlink code itself does not function. it needs the #.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**ussenterprise** · 07-10-2013, 04:35 PM

See my post above. I think that'll get it for you.

**sgrande01** · 07-10-2013, 04:37 PM

i'm starting to think this simply won't work in VB.

just as a reminder, it has to be in this format:

=HYPERLINK("#'OPRATIONS1'!BK9","OPRATIONS1")

**sgrande01** · 07-10-2013, 04:37 PM

still getting the application defined error...

**ussenterprise** · 07-10-2013, 04:40 PM

Let me take another look.

**sgrande01** · 07-10-2013, 04:46 PM

maybe let's try to simplify it by taking the ranges out and just making the cells static. once the code will go into excel correctly then maybe move the dynamic ranges in?

**ussenterprise** · 07-10-2013, 04:49 PM

Hm... strangeness on this one.
I'll have to continue tomorrow.

**ussenterprise** · 07-10-2013, 04:49 PM

Originally Posted by sgrande01

maybe let's try to simplify it by taking the ranges out and just making the cells static. once the code will go into excel correctly then maybe move the dynamic ranges in?

Hm. One sec...

**sgrande01** · 07-10-2013, 04:54 PM

something that is also strange - this works fine in a msgbox but not when assigning it to a cell. makes me think something is going on in the back end or that you can't bring this formula in from vb to excel?

Please Login or Register  to view this content.

the above code is set in a new workbook with currentrow the only variable

**ussenterprise** · 07-10-2013, 04:58 PM

Please Login or Register  to view this content.

This appears to work as intented. At least in the MsgBox.
And... I see what you mean. I have an idea, but it may take me a while to research it...

**ussenterprise** · 07-10-2013, 05:05 PM

Please Login or Register  to view this content.

Here's my first attempt. Getting an error. I'll continue tomorrow.

**sgrande01** · 07-10-2013, 05:27 PM

i got it:

Please Login or Register  to view this content.

thanks for the help!

Hyperlink Syntax Issue

LinkBack

Thread Tools

Rate This Thread

Display

Hyperlink Syntax Issue

Re: Hyperlink Syntax Issue

Re: Hyperlink Syntax Issue

Re: Hyperlink Syntax Issue

Re: Hyperlink Syntax Issue

Re: Hyperlink Syntax Issue

Re: Hyperlink Syntax Issue

Re: Hyperlink Syntax Issue

Re: Hyperlink Syntax Issue

Re: Hyperlink Syntax Issue

Re: Hyperlink Syntax Issue

Re: Hyperlink Syntax Issue

Re: Hyperlink Syntax Issue

Re: Hyperlink Syntax Issue

Re: Hyperlink Syntax Issue

Re: Hyperlink Syntax Issue

Re: Hyperlink Syntax Issue

Re: Hyperlink Syntax Issue

Re: Hyperlink Syntax Issue

Re: Hyperlink Syntax Issue

Re: Hyperlink Syntax Issue

Re: Hyperlink Syntax Issue

Re: Hyperlink Syntax Issue

Re: Hyperlink Syntax Issue

Re: Hyperlink Syntax Issue

Re: Hyperlink Syntax Issue

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions