Yet Another Cycling Forum

General Category => The Knowledge => Ctrl-Alt-Del => Topic started by: Somnolent on 09 October, 2016, 02:34:14 pm

Title: A little VBA help please
Post by: Somnolent 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.

Title: Re: A little VBA help please
Post by: SoreTween 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.

Title: Re: A little VBA help please
Post by: Somnolent on 09 October, 2016, 03:38:22 pm

Fabulous - thanks.
/Goes looking for virtual beer emoticon/