VBA FOR EACH + IF STATEMENT

[smunk]

hi guys! Hope you can help me here as I got stuck.

I have a column of phone numbers (over 140) and I want to create a VBA command allowing users to copy phone number by double clicking on the cell.
I know Javascript, but doesnt know VB very well.

I think my code should look something like:


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

For Each c In Worksheets("Sheet1").Range("H13:H200").Cells

If Target.Address = c Then

Range(c).Copy

End If
Next

Cancel = True
End Sub



Please, tell me where am i wrong? Is my logic correct?

 

[smunk]

Indentation is not needed for the code to work properly but most people always use it because it makes it easy to follow the flow of the code especially when you use loops and "With .... End With" statements. I highly recommend using it.

You don't need a loop because you can double click only one cell at a time.

oh ok it makes sense. Can u pls tell me how can I add 2nd If statement for another column? this column contains email address. So same logic.

That doesnt work:

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

If Target.CountLarge > 1 Then Exit Sub
If Intersect(Target, Range("L4:L200")) Is Nothing Then Exit Sub
Target.Copy

Else: If Target.CountLarge > 1 Then Exit Sub
If Intersect(Target, Range("K4:K200")) Is Nothing Then Exit Sub
Target.Copy

Cancel = True
End Sub

 

[mumps]

Try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("K:L")) Is Nothing Then Exit Sub
    Target.Copy
    Cancel = True
End Sub

 

[smunk]

thanks! will try now

 

[smunk]

Try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("K:L")) Is Nothing Then Exit Sub
    Target.Copy
    Cancel = True
End Sub

works fine! thanks. yeah i get the logic. so we are just using 2 columns as one data set

 

[mumps]

You are very welcome. K:L restricts the doubleclick event to any cell in those 2 columns.