Insert hyperlink in column until row x then insert in next column

[CutterJaxx]

Hi all,

Running Windows XP, Excel 2003.

I have written a macro which creates new worksheets, allows the user to enter the name for the sheet and then creates a hyperlink to the new sheet on a menu worksheet.

The hyperlink is inserted in the next empty cell in column B (starting at cell B7) and offset by two rows from the last. I want to only insert the hyperlinks in column B until Row 29. At this point I need any new hyperlinks to be inserted in column D, then column F and so on. How can I do this in code???

Below is the existing code.

'Add HyperLink on Menu Page to new datasheet
    Range("B7").Select
    Do
        If IsEmpty(ActiveCell) = False Then
        ActiveCell.Offset(2, 0).Select
        End If
        Loop Until IsEmpty(ActiveCell) = True
 
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & AreaName & "'!A1", TextToDisplay:=AreaName

Any and all assistance appreciated
Cutter

 

[xlbo]

Would suggest a column offset indicator
dim colOffset as integer, lRow as long
colOffset = 0

Do
lrow = cells(65536,activecell.column).end(xlup).row
if lRow > 29 then colOffset = colOffset + 2
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(2, colOffset).Select
End If
Loop Until IsEmpty(ActiveCell) = True

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & AreaName & "'!A1", TextToDisplay:=AreaName

May need some tweaking but hopefully you get the idea...


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question