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

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


"Nick Gilbert" wrote:

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

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

--


Gary


"Nick Gilbert" <(E-Mail Removed)> wrote in message
news:u%(E-Mail Removed)...
> 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....

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

"Nick Gilbert" wrote:

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

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

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
news:(E-Mail Removed)...
> 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
>
> --
>
>
> Gary
>
>
> "Nick Gilbert" <(E-Mail Removed)> wrote in message
> news:u%(E-Mail Removed)...
>> 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....
>
>