Shorten the code

[Ongbey]

Hi, beIow you will see my Excel VBA code. It will expand to until A120. Could you please shorten the code?
Thanks,

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If Target.Range.Address = "$A$20" Then [AR1] = 0: [A1] = Range("A20").Value: Call BOM: Exit Sub
If Target.Range.Address = "$A$21" Then [AR1] = 0: [A1] = Range("A21").Value: Call BOM: Exit Sub
If Target.Range.Address = "$A$22" Then [AR1] = 0: [A1] = Range("A22").Value: Call BOM: Exit Sub
If Target.Range.Address = "$A$23" Then [AR1] = 0: [A1] = Range("A23").Value: Call BOM: Exit Sub
If Target.Range.Address = "$A$24" Then [AR1] = 0: [A1] = Range("A24").Value: Call BOM: Exit Sub
If Target.Range.Address = "$A$25" Then [AR1] = 0: [A1] = Range("A25").Value: Call BOM: Exit Sub
If Target.Range.Address = "$A$26" Then [AR1] = 0: [A1] = Range("A26").Value: Call BOM: Exit Sub
If Target.Range.Address = "$A$27" Then [AR1] = 0: [A1] = Range("A27").Value: Call BOM: Exit Sub
If Target.Range.Address = "$A$28" Then [AR1] = 0: [A1] = Range("A28").Value: Call BOM: Exit Sub
If Target.Range.Address = "$A$29" Then [AR1] = 0: [A1] = Range("A29").Value: Call BOM: Exit Sub
If Target.Range.Address = "$A$30" Then [AR1] = 0: [A1] = Range("A30").Value: Call BOM: Exit Sub
End Sub

 

[Alex Blakenburg]

I don't normally use that short form syntax, but see if this does what you want.

If Not Intersect(Target, Range("A20:A120")) Is Nothing Then
    [AR1] = 0: [A1] = Target.Value: Call BOM: Exit Sub
End If

If it is not doing anything else you don't need the Exit Sub part
If you have a change event running as well you will want to add Application.EnableEvents = False (and a True at the end of the routine)

 

[footoo]

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim rng As Range: Set rng = [A20:A120]
If Target.Count > 1 Then Exit Sub
If Not Intersect(rng, Target) Is Nothing Then [AR1] = 0: [A1] = Target.Value: Call BOM
End Sub

 

[Ongbey]

I don't normally use that short form syntax, but see if this does what you want.

If Not Intersect(Target, Range("A20:A120")) Is Nothing Then
    [AR1] = 0: [A1] = Target.Value: Call BOM: Exit Sub
End If

If it is not doing anything else you don't need the Exit Sub part
If you have a change event running as well you will want to add Application.EnableEvents = False (and a True at the end of the routine)

Thanks for the solution. But Excel give an error "Type mismatch" for "if not ..." sentence. Our version is 2013, is t problem?

 

[Ongbey]

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim rng As Range: Set rng = [A20:A120]
If Target.Count > 1 Then Exit Sub
If Not Intersect(rng, Target) Is Nothing Then [AR1] = 0: [A1] = Target.Value: Call BOM
End Sub

Thanks for the solution. But Excel give an error "Object doesn't support" for "if target..." sentence. Our version is 2013, is t problem?

 

[Alex Blakenburg]

No not a 2013 issue. Target is a range in most event macros but not in the follow hyperlink event.
Give this a try:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim rngTarget As Range
    Set rngTarget = Target.Range
   
    If Not Intersect(rngTarget, Range("A20:A120")) Is Nothing Then
        Application.EnableEvents = False
        [AR1] = 0: [A1] = rngTarget.Value
        Call BOM: Exit Sub
        Application.EnableEvents = True
    End If
End Sub

 

[Ongbey]

No not a 2013 issue. Target is a range in most event macros but not in the follow hyperlink event.
Give this a try:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim rngTarget As Range
    Set rngTarget = Target.Range
  
    If Not Intersect(rngTarget, Range("A20:A120")) Is Nothing Then
        Application.EnableEvents = False
        [AR1] = 0: [A1] = rngTarget.Value
        Call BOM: Exit Sub
        Application.EnableEvents = True
    End If
End Sub

Great. Code worked. thank you !

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.