VBA - format a range of cells as hyperlink?

Hi

I have a spreadsheet which contains the locations of some files starting at
C2 and going down as far as required. What I would like to do is put some
VBA code that either turns the text in C2:C? to a hyperlink to the file
(preferred) or put a hyperlink to the file in D2:D?. I've tried but as yet
with no success.

Can anyone help?

Thanks
SteveB

"Steveb" <as-if@noway.com> skrev i en meddelelse
news:PbadnZfIStYtlFbZRVnyhg@bt.com...
> Hi
>
> I have a spreadsheet which contains the locations of some files starting
> at C2 and going down as far as required. What I would like to do is put
> some VBA code that either turns the text in C2:C? to a hyperlink to the
> file (preferred) or put a hyperlink to the file in D2:D?. I've tried but
> as yet with no success.
>
> Can anyone help?
>
> Thanks
> SteveB

Hi Steve

Here's one way to accomplish it:

Sub ToHyperlink()
'Leo Heuser, 29-7-2006
Dim Cell As Range
Dim CheckRange As Range

On Error Resume Next

Set CheckRange = Sheets("Sheet1").Range("C2")
Set CheckRange = Range(CheckRange, Cells(ActiveSheet.Rows.Count, _
CheckRange.Column).End(xlUp))

For Each Cell In CheckRange.Cells
Cell.Parent.Hyperlinks.Add Anchor:=Cell, Address:= _
Cell.Value, TextToDisplay:=Cell.Value
Next Cell

On Error GoTo 0

End Sub

--
Best regards
Leo Heuser

Followup to newsgroup only please.

"Leo Heuser" <leo.heuser@DROPTHISadslhome.dk> wrote in message
news:uVtOa7usGHA.1632@TK2MSFTNGP06.phx.gbl...
> "Steveb" <as-if@noway.com> skrev i en meddelelse
> news:PbadnZfIStYtlFbZRVnyhg@bt.com...
>> Hi
>>
>> I have a spreadsheet which contains the locations of some files starting
>> at C2 and going down as far as required. What I would like to do is put
>> some VBA code that either turns the text in C2:C? to a hyperlink to the
>> file (preferred) or put a hyperlink to the file in D2:D?. I've tried but
>> as yet with no success.
>>
>> Can anyone help?
>>
>> Thanks
>> SteveB
>
> Hi Steve
>
> Here's one way to accomplish it:
>
> Sub ToHyperlink()
> 'Leo Heuser, 29-7-2006
> Dim Cell As Range
> Dim CheckRange As Range
>
> On Error Resume Next
>
> Set CheckRange = Sheets("Sheet1").Range("C2")
> Set CheckRange = Range(CheckRange, Cells(ActiveSheet.Rows.Count, _
> CheckRange.Column).End(xlUp))
>
> For Each Cell In CheckRange.Cells
> Cell.Parent.Hyperlinks.Add Anchor:=Cell, Address:= _
> Cell.Value, TextToDisplay:=Cell.Value
> Next Cell
>
> On Error GoTo 0
>
> End Sub
>
> --
> Best regards
> Leo Heuser
>
> Followup to newsgroup only please.
>
Thanks Leo, works perfectly.

SteveB

"Steveb" <as-if@noway.com> skrev i en meddelelse
news:68udnQYnTd8islbZnZ2dnUVZ8qWdnZ2d@bt.com...
>
> "Leo Heuser" <leo.heuser@DROPTHISadslhome.dk> wrote in message
> news:uVtOa7usGHA.1632@TK2MSFTNGP06.phx.gbl...
>> "Steveb" <as-if@noway.com> skrev i en meddelelse
>> news:PbadnZfIStYtlFbZRVnyhg@bt.com...
>>> Hi
>>>
>>> I have a spreadsheet which contains the locations of some files starting
>>> at C2 and going down as far as required. What I would like to do is put
>>> some VBA code that either turns the text in C2:C? to a hyperlink to the
>>> file (preferred) or put a hyperlink to the file in D2:D?. I've tried but
>>> as yet with no success.
>>>
>>> Can anyone help?
>>>
>>> Thanks
>>> SteveB
>>
>> Hi Steve
>>
>> Here's one way to accomplish it:
>>
>> Sub ToHyperlink()
>> 'Leo Heuser, 29-7-2006
>> Dim Cell As Range
>> Dim CheckRange As Range
>>
>> On Error Resume Next
>>
>> Set CheckRange = Sheets("Sheet1").Range("C2")
>> Set CheckRange = Range(CheckRange, Cells(ActiveSheet.Rows.Count, _
>> CheckRange.Column).End(xlUp))
>>
>> For Each Cell In CheckRange.Cells
>> Cell.Parent.Hyperlinks.Add Anchor:=Cell, Address:= _
>> Cell.Value, TextToDisplay:=Cell.Value
>> Next Cell
>>
>> On Error GoTo 0
>>
>> End Sub
>>
>> --
>> Best regards
>> Leo Heuser
>>
>> Followup to newsgroup only please.
>>
> Thanks Leo, works perfectly.
>
> SteveB
>

You're welcome, Steve, and thanks for the feedback :-)

Leo Heuser