VBA script to create hyperlinks based on cell info?

Question

Howdy,

I would like to have a script that inserts a hyperlink into column C based on what the value in column B is.

The URL I would like to direct to will depend on what is in a cell in that row.  If "batman" is in there, I need a link to the "batman" area of the website.  If "superman", I need a link to "superman" in the website.  See the following example: [ignore the fact that the URL isn't real, it's just to simplify it for explanation]

Here's the formula I am using for now:

=HYPERLINK("https://www.google.com/batman/Status!openForm&Id="&B225,"Link")

So, if in cell B225 I have "batman-1248", the link would take me to https://www.google.com/batman/Status!openForm&Id=1234

This works so far, however, see in the URL where I have "batman"?  This is only correct if "batman" is mentioned in the cell B225.  I have many entries that are either Batman, Superman, Spiderman and will need the URL to change depending on which is in cell B225 (or the corresponding cell in column B for the same row).

In other words, if B225 is "spiderman-193" and I use the previously mentioned formula, it will send me to https://www.google.com/batman/Status!openForm&Id=spiderman-193.              ...but my spiderman stuff isn't in the /batman/ area of the site, it should be /spiderman/Status!openForm...

If I have a cell with "superman-1294", I would like the URL to change the /batman/ to be /superman/ and etc for spiderman, "/batman/" needs to be "/spiderman/"

I figure this shouldn't be too hard, I have thought about doing an "IF" statement, but figure that could get kind of long...something like "IF B225 contains "batman", then URL with /batman/, else IF B225 contains "spiderman", then URL with /spiderman/ ... but that could get confusing and isn't (I don't think) the "correct" way to do this.

Thanks for any help, and I would be glad to clarify if the above is confusing or convoluted.

Answer 1

Does this cell formula do what you need:

=HYPERLINK("https://www.google.com/"&LEFT(B225,FIND("-",B225)-1)&"/Status!openForm&Id="&RIGHT(B225,LEN(B225)-FIND("-",B225)),"Link")

Hope that helps.

Cheers

Rich

Answer 2

Aw, good thinking...

I'm sorry, I was trying to make it simple but forgot that it's better to just put it all out there.

...so the URL part that is /batman/  will NOT correlate with the text in the next column, in other words, the URL will actually look like:

https://www.google.com/batmanv5.nsf/Status!openForm&Id=

if it's Spiderman,

https://www.google.com/spiderman\_v5.nsf/Status!openForm&Id=

So, see how while it is "spiderman" in the URL, it's not exactly "spiderman"...

Huh?

The only diference I see is the underscore:

batmanv5.nsf

spiderman_v5.nsf

(the v5.nsf is the same for both?)

Could you just add an IF function to the formula:

IF(OR(LEFT(B225,FIND("-",B225)-1)={"spiderman","wonderwoman"}),"_","")

so that an underscore is added if spiderman or wonderwoman (for example) is found...

Using helper columns would help you at this point.

I agree, it's better to "put it all out there", but I'm not sure you have done that yet!

Cheers

Rich

Answer 3

Aw, good thinking...

I'm sorry, I was trying to make it simple but forgot that it's better to just put it all out there.

...so the URL part that is /batman/  will NOT correlate with the text in the next column, in other words, the URL will actually look like:

https://www.google.com/batmanv5.nsf/Status!openForm&Id=

if it's Spiderman,

https://www.google.com/spiderman\_v5.nsf/Status!openForm&Id=

So, see how while it is "spiderman" in the URL, it's not exactly "spiderman"...

Answer 4

I have 4 main ones, I believe. 

Sorry for using superheores, but the actual values I'm using are confusing, and it's easier to "see" the superhero names...i'll obviously edit that when I put in the code.

But does the question make sense?

Answer 5

How many comic book heroes do you need to accommodate?