Solved

Add a substring to an existing hyperlink address using VBA

Posted on 2013-11-15
3
331 Views
Last Modified: 2013-11-18
Dear Experts:

I would like to run a macro that performs the following task

Loop thru all the cells in Column F.
... As soon as the macro hits a grey shaded cell (RGB 250, 250, 250) ...
... go three cells to the left (Column C) ...
... and add the string '_copy' at the end of the stored hyperlink (but before the file extension)  that is stored in that cell

For example:

First grey cell in F 147
The hyperlink in C 147 has the following address : C:\myfolder\47-338-44-09.tif
The new hyperlink address after running the macro will be: C:\myfolder\47-338-44-09_copy.tif
That is all of the stored hyperlinks have the following make-up
C:\myfolder\##-###-##-##.tif

After running the macro the hyperlink addresses will say:
C:\myfolder\##-###-##-##_copy.tif

Help is much appreciated.

Thank you very much in advance.

Regards, Andreas
0
Comment
Question by:AndreasHermle
  • 2
3 Comments
 
LVL 6

Accepted Solution

by:
Michael earned 500 total points
Comment Utility
Hi Andreas,

the following code is one approach to do this:

Sub addToHyperlink()
    Dim rng As Range, cell As Range
    Dim lr As Integer
    Dim strHyperlink As String
    
    lr = Cells(Rows.Count, 3).End(xlUp).Row
    Set rng = Range(Cells(1, 6), Cells(lr, 6))
    
    For Each cell In rng
        If cell.Interior.Color = RGB(250, 250, 250) Then
            strHyperlink = cell.Offset(, -3).Value
            strHyperlink = Mid(strHyperlink, 1, Len(strHyperlink) - 4) & "_copy" & Right(strHyperlink, 4)
            cell.Offset(, -3).Value = strHyperlink
        End If
    Next
End Sub

Open in new window


I hope this works for you. It assumes that every corresponding cell in column C of a grey cell in column F, contains a hyperlink. If not, it will throw in an error.

Remember to test the code on a copy of your workbook.

Joop
0
 

Author Comment

by:AndreasHermle
Comment Utility
Hi Joop,

thank you very much for your swift help. I will test the code tomorrow and let you know.

Again, thank you very much for your professional help.

Regards, Andreas
0
 

Author Closing Comment

by:AndreasHermle
Comment Utility
Hi Joop,

works like a charm, thank you very much for your great and professional support.

Regards, Andreas
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now