CODEWITHSUNDEEP
excelvbaloops
nurotriyono
nurotriyono

Reputation: 13

How to Loop Function VBA Excell

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?

Upvotes: 1

Views: 60

Answers (2)

BigBen
BigBen

Reputation: 50162

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

Upvotes: 2

Gugax
Gugax

Reputation: 11

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

Upvotes: 1

Related Questions