Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Add a substring to an existing hyperlink address using VBA

Posted on 2013-11-15
3
Medium Priority
?
371 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 6

Accepted Solution

by:
Michael earned 2000 total points
ID: 39651465
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
ID: 39654473
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
ID: 39656497
Hi Joop,

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

Regards, Andreas
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

719 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