How to Loop Function VBA Excell

Question

I want to apply this code to Cells (P2 to P300) but i don't know how to write it.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("P2")) Is Nothing Then
    Dim Fl2 As String
    Fl2 = Range("O2")
    Call Shell("explorer.exe /select," & Fl2, vbNormalFocus)
    End If

    If Not Intersect(Target, Range("P3")) Is Nothing Then
    Dim Fl3 As String
    Fl3 = Range("O3")
    Call Shell("explorer.exe /select," & Fl3, vbNormalFocus)
    End If

How to get more easy way?

BigBen · Accepted Answer · 2020-09-25 01:49:13Z

2

Cleaned up a bit using Intersect on the entire range and Offset to refer to the corresponding cell in column O.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Not Intersect(Target, Me.Range("P2:P200")) Is Nothing Then
        Shell "explorer.exe /select," & Target.Offset(,-1).Value, vbNormalFocus
    End If

End Sub

edited Sep 25, 2020 at 1:49

answered Sep 25, 2020 at 1:39

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gugax · Accepted Answer · 2020-09-25 01:39:59Z

1

The Insersect statement accepts multi-cell range

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("P2:P300")) Is Nothing Then
        Dim Fl As String
        Fl = Range("O" & Target.Row)
        Call Shell("explorer.exe /select," & Fl, vbNormalFocus)
    End If
End Sub

answered Sep 25, 2020 at 1:39

Gugax

111 bronze badge

Collectives™ on Stack Overflow

How to Loop Function VBA Excell

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related