Hyperlink concatenated exceed 255 characters

[thomas854]

Hello guys,

First thank you for this great forum and all informations !

Well, I will explain my issue.


I need to open a Hyperlink concatenated in a cell (by example A1) who exceed 255 characters.
I tried a lot of VBA, macros and all have an issue...

The cell must be clickable and show a hyperlink name like "CLICK HERE" and need to open the very long url in the default browser.

Please I need your help.

 

[admiral100]

This is because column H contains a formula.
The corrected code will insert the link (with the click option) into the J column


Sub hyp()




Dim rng As Range
Set rng = Application.Range("H4:H299")
Dim cel As Range
Dim i As Integer
i = 1




For Each cel In rng


ActiveSheet.Hyperlinks.Add Anchor:=cel.Offset(0, 2), Address:=cel.Value, ScreenTip:="My Link", TextToDisplay:="Click Here" & i


i = i + 1




Next




End Sub

 

[thomas854]

Hi,

Thanks again for your reply,

This time the 2 issues are solved.


- Well, now all hyperlinks are created in the column "J", so how to set the new hyperlinks in the column "H" as I moved the actual concatenated formula in the column "V"?

- Also the hyperlink name are created and are working but look like this : CLICK HERE1, CLICK HERE2, CLICK HERE3,CLICK HERE4...
It is possible to have the hyperlink name only as "CLICK HERE" without the line number?



Thanks again & again.

 

[thomas854]

Well, I solved that by myself!

Now all is working fine! Thank you again for your help and your time dear admiral100, you are the man!
Maybe for fun how in the VBA script add a condition (if) to create the hyperlink name only if they are data entered (in lineX) in the column "E"?


And if you agree and you have enough time I need again your help, always for the same file...

The column "G" open a local folder to next add a picture in it...
It is possible when I click on the link to open a dialog box to search the right picture, next move this picture in the folder with the name (of the right line) in the column A&B?


Thanks!!!