Author Topic: A little VBA help please  (Read 741 times)

A little VBA help please
« on: 09 October, 2016, 02:34:14 pm »
I've a column of code numbers in an Excel sheet & want to add to each code number a hyperlink - each link will have the same prefix, followed by the code number.
Display     Link
A1            http://www.diddly.wotsit/A1
A2            http://www.diddly.wotsit/A2
B1            http://www.diddly.wotsit/B1
B2            http://www.diddly.wotsit/B2
B3            http://www.diddly.wotsit/B3

I could concatenate and have the links in an adjacent column to the plain text code numbers but that's a bit meh.
As my (very little) VBA learning is now more than two decades in the past I thought I could at least record a crude macro to do this... but failing miserably.
Doubtless there is someone out there for whom this is trivially easy.

SoreTween

  • Most of me survived the Pennine Bridleway.
Re: A little VBA help please
« Reply #1 on: 09 October, 2016, 02:55:36 pm »
Code: [Select]
Sub Macro1()

    Dim MyPrefix, CellLink As String
    MyPrefix = "http://www.diddly.wotsit/"
    Do
        CellLink = MyPrefix & ActiveCell.Text
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
            CellLink, TextToDisplay:=ActiveCell.Text
        ActiveCell.Offset(1, 0).Select
    Loop Until ActiveCell.Text = ""
End Sub


Select the cell at the top of the column and this will run downwards until it encounters an empty cell.
2023 targets: Survive. Maybe.
There is only one infinite resource in this universe; human stupidity.

Re: A little VBA help please
« Reply #2 on: 09 October, 2016, 03:38:22 pm »
Fabulous - thanks.
/Goes looking for virtual beer emoticon/