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

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

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

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

"Craig" wrote in message
ups.com...
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

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

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.

--
Regards,
Tom Ogilvy

"Craig" wrote in message
ups.com...
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

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

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 . . .