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?

 

[mumps]

Try:

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

 

[smunk]

Try:

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

nope it not working..

 

[mumps]

It worked for me. Did you place the macro in the worksheet code module?

 

[smunk]

It worked for me. Did you place the macro in the worksheet code module?

 

[smunk]

It worked for me. Did you place the macro in the worksheet code module?

but i figured out ur logic. did a bit of googling:

so:

- Exit if more than one cell updated at a time
- Exit if cell updated is outside of designated range

and then copy the target cell.

But its not working for me for some reason

 

[mumps]

I can't reproduce the error. I tried it several times and it worked properly. Could you use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[smunk]

I can't reproduce the error. I tried it several times and it worked properly. Could you use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

not sure what i have just done but its working OK now. Thanks!

Is indentation important in VB? I am trying to figure what i had wrong before

 

[smunk]

I can't reproduce the error. I tried it several times and it worked properly. Could you use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

and do u think it can be solved with loop and if statement? can u have a look if the code i tried originally can be improved so it works? Just curios now.

 

[mumps]

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.

and do u think it can be solved with loop and if statement?

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