applying a macro for a specific field to a range of fields

  • Thread starter Thread starter Craig
  • Start date Start date
C

Craig

I have a macro that takes information from two columns and builds a
hyperlink in another column. I'm pretty new at this, so while I was
writing the macro I wrote it for one specific cell, using information
from two other cells. I want to expand the macro so that it applies to
every cell in the column, but I don't know how to do this. Can anyone
show me how, here's what I have so far.

Sub makehyperlinks()
Range("E2").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection,
Address:="c:\test\job" & Range("B2") & "-" & Format(Range("A2"),
"yyyy-mm-dd") & "TimeCard" & ".xls", TextToDisplay:="File"

End Sub
 
Sub makehyperlinks()
Dim Lrow As Long
Dim rng As Range
Dim c As Range
Lrow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("E2:E" & Lrow)
For Each c In rng
ActiveSheet.Hyperlinks.Add Anchor:=c, Address:= _
"c:\test\job" & Range("B"& Lrow) & "-" & Format(Range("A"& Lrow), _
"yyyy-mm-dd") & "TimeCard" & ".xls", TextToDisplay:="File"
Next c
End Sub

Untested, but same layout as some code I use. I use String Variables in the
Address:= instead of cell references.
For example: Add 2 more variables
Dim myString As String, myDate As String
Then following the For Each line
For Each c In rng
myString = c.Offset(0,-3).Value 'Col B
myDate = c.Offset(0,-4).Value 'Col A
ActiveSheet.Hyperlinks.Add Anchor:=c, Address:= _
"c:\test\job" & myString & "-" & Format(myDate), _
"yyyy-mm-dd") & "TimeCard" & ".xls", TextToDisplay:="File"
Next c

You may have better luck using it this way.
Mike F
 
You can do this without a macro using the Hyperlink worksheet function.

Write the formula, then drag fill down the column.

See Excel help for details.
 
Thanks for the replies.

I've tried Mike's first code, and it just puts the same link in every
cell.

Tom - I tried to use the formula, but one of the parts of the filename
is a date, so the file name would be c:\test\job6-2004-06-02.xls and
the formula links to c:\test\job6-38140.xls

I'm trying Mike's second code now . . .
 
Back
Top