Offset not working in VB

[that_one_girl]

Hello Everyone,

I have a column with a drop-down list of staff to select for a particular task (starts in H3)

I have the following code programmed so that after the Employees have been assigned, management can run the macro to change the employees initials from the drop-down list, into an email hyperlink that he can just click it to send them an email notification of the assignment.

Below is my code. When I run it, it works great on the first cell!

But need it to skip anything that is already a hyperlink, and I need it to move down one row, same column, and repeat until it hits an empty cell.

Can anyone help?

Sub Hyperlink_Function()

Range("H3").Select

If ActiveCell.FormulaR1C1 = "VH" = True Then
ActiveCell.Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"mailto:VHeza@dir.ca.gov?subject=You%20have%20a%20new%20training%20assignment" _
, TextToDisplay:="VH"

ElseIf ActiveCell.FormulaR1C1 = "ML" = True Then
ActiveCell.Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"mailto:Mloupe@dir.ca.gov?subject=You%20have%20a%20new%20training%20assignment" _
, TextToDisplay:="ML"

ElseIf ActiveCell.FormulaR1C1 = "MC" = True Then
ActiveCell.Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"mailto:MChristian@dir.ca.gov?subject=You%20have%20a%20new%20training%20assignment" _
, TextToDisplay:="MC"


ElseIf ActiveCell.FormulaR1C1 = "M | S" = True Then
ActiveCell.Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"mailto:MChristian@dir.ca.gov;%20SBFlores@dir.ca.gov?subject=You%20have%20a%20new%20training%20assignment%20with%20a%20partner" _
, TextToDisplay:="M | S"


ElseIf ActiveCell.FormulaR1C1 = "SF" = True Then
ActiveCell.Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"mailto:SBFlores@dir.ca.gov?subject=You%20have%20a%20new%20training%20assignment" _
, TextToDisplay:="SF"

ElseIf ActiveCell.FormulaR1C1 = "AL" = True Then
ActiveCell.Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"mailto:ALazzara@dir.ca.gov?subject=You%20have%20a%20new%20training%20assignment" _
, TextToDisplay:="AL"

ActtiveCell.Offset(1, 0).Select

ElseIf ActiveCell.FormulaR1C1 = "" = True Then

End If

End Sub

 

[Trebor76]

Hi there,

This will stop at the first cell from H3 that doesn't have a hyperlink in it - is that what you're after?

Option Explicit
Sub Macro1()

    Application.ScreenUpdating = False
    
    Range("H3").Select
    
    Do Until ActiveCell.Hyperlinks.Count = 0 'Adapted from www.ozgrid.com/forum/showthread.php?t=74922
        ActiveCell.Offset(1, 0).Select
    Loop
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[that_one_girl]

Almost...............I need it to stop at a cell with a hyperlink, and/or a cell that is blank (no text)

Hi there,

This will stop at the first cell from H3 that doesn't have a hyperlink in it - is that what you're after?

Option Explicit
Sub Macro1()

    Application.ScreenUpdating = False
    
    Range("H3").Select
    
    Do Until ActiveCell.Hyperlinks.Count = 0 'Adapted from www.ozgrid.com/forum/showthread.php?t=74922
        ActiveCell.Offset(1, 0).Select
    Loop
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[that_one_girl]

I was able to use your code and modify it, and I got it working now! THANK YOU!!

Hi there,

This will stop at the first cell from H3 that doesn't have a hyperlink in it - is that what you're after?

Option Explicit
Sub Macro1()

    Application.ScreenUpdating = False
    
    Range("H3").Select
    
    Do Until ActiveCell.Hyperlinks.Count = 0 'Adapted from www.ozgrid.com/forum/showthread.php?t=74922
        ActiveCell.Offset(1, 0).Select
    Loop
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[Trebor76]

Glad to hear it and you're welcome