Applying a macro to a range of cells

[Nick Gilbert]

Hi,

I've written a macro which creates URLs from part numbers in the active
cell using a part number in the cell immediately left. However it only
works if I select a single cell. As soon as I select multiple cells, the
macro seems to have no effect.

Can somebody please tell me how I modify it so it works if I select an
entire column:

Sub Macro2()
Dim leftCellValue
leftCellValue = ActiveCell.Offset(0, -1).Value
If Left(leftCellValue, 2) = "92" Then

ActiveCell.Hyperlinks.Add Anchor:=Selection,
Address:="http://website/product.aspx?part=" & ActiveCell.Offset(0,
-1).Value
ActiveCell.Value = "view"
End If
End Sub

Thanks,

Nick....

 

[Pops Jackson]

I would use a "Do - Loop until Activecell.Offset(0, -1) = "" " with
Activecell.Offset(1,0) just before "Loop..."

 

[Gary Keramidas]

here's one way

Sub Macro2()
Dim leftCellValue As Long
Dim cell As Range
Dim lastrow As Long, i As Long
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "B").End(xlUp).Row

For Each cell In ws.Range("B2:B" & lastrow)
leftCellValue = Left(cell.Value, 2)
If leftCellValue = "92" Then
cell.Offset(0, 1).Hyperlinks.Add Anchor:=cell.Offset(0, 1), _
Address:="http://website/product.aspx?part=" & cell.Value
cell.Offset(0, 1).Value = "view"
End If
Next
End Sub

 

[Barb Reinhardt]

Try this:

Sub Macro2()
Dim r As Range
Dim myRange As Range
Dim leftCellValue

Set myRange = Selection
For Each r In myRange
If r.Column >= 2 Then
leftCellValue = r.Offset(0, -1).Value
If Left(leftCellValue, 2) = "92" Then
r.Hyperlinks.Add Anchor:=r, _
Address:="http://website/product.aspx?part=" & r.Offset(0,
-1).Value
r.Value = "view"
End If
End If
Next r

End Sub



HTH,
Barb Reinhardt

 

[Gary Keramidas]

forgot to mention you need to change the range and sheetname to what you're
using