VBA - Help building upon "copy & paste to new sheet cell on doubleclick" with isnumber & istext

[Zetroza]

Hello, the code provided below is from my previous sucess (A long time ago) on this board and i'd like to ask for help again.

a	1
b	2
c	3
d	4
e	5
f	6
g	7
h	8
i	9
j	10

<tbody>
</tbody>

Assuming the above table starts in "A1" the below vba code should copy and paste any such doubleclicked cell into sheet2 (from columnA) or Sheet3 (from columnB).


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("a1:a10")) Is Nothing Then
Cancel = True
If Target.Row > 1 And Len(Target.Value) Then Worksheets("Sheet2").Range("a1").Value = Target.Value
Worksheets("Sheet2").Activate
ElseIf Not Intersect(Target, Range("b1:b10")) Is Nothing Then
Cancel = True
If Target.Row > 1 And Len(Target.Value) Then Worksheets("Sheet3").Range("a1").Value = Target.Value
Worksheets("Sheet3").Activate
End If
End Sub

Great! But now looking at the next table below, i need upon doubleclick - if such cell is text to copy to sheet2 and if cell is a number to copy to sheet3.

a	1
b	2
c	3
d	4
5	e
6	f
7	g
h	8
i	9
j	10

<tbody>
</tbody>

Any ideas or thoughts welcome, hopefully the above code works well for those who are interested in using it and/or building upon it.

Many thanks,

 

[Zetroza]

You have delivered a perfect working piece of code everytime and after staring at it for most of my day its all very suddenly become so much clearer.
These small snippets of code have taught me so much,
Thank you,

 

[James006]

You have delivered a perfect working piece of code everytime and after staring at it for most of my day its all very suddenly become so much clearer.
These small snippets of code have taught me so much,
Thank you,

Glad you could fix your problem ...

Thanks a lot ... for your very kind thanks :wink:

 

[Zetroza]

Hello again,

**Adding a third if** (or elseif)

At this point it is trial and error for me, although today has been mostly all error.
Using the code above as my new template I am trying to implement a third argument here where by

' Text goes to Sheet 2, elseif Number value (<50000) goes to sheet 3, else Number value (>=50000) goes to sheet 4

rather than "isnumeric" I need another "if" to differentiate between values in the target cell as < or >= 50000 as well as continue to identify when text.

Any further help would be greatly appreciated,
Many thanks,

 

[James006]

Hello,

Regarding your latest request, below is the code to be tested ...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
' Text goes to Sheet 2
' Number < 50000 goes to Sheet 3
' Number >=50000 goes to Sheet 4


If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1:B10")) Is Nothing Then Exit Sub


Dim lsh2 As Long
Dim lsh3 As Long
Dim lsh4 As Long


lsh4 = IIf(Worksheets("Sheet4").Range("A1") = "", 1, Worksheets("Sheet4").Range("a65536").End(xlUp).Row + 1)
lsh3 = IIf(Worksheets("Sheet3").Range("A1") = "", 1, Worksheets("Sheet3").Range("a65536").End(xlUp).Row + 1)
lsh2 = IIf(Worksheets("Sheet2").Range("A1") = "", 1, Worksheets("Sheet2").Range("a65536").End(xlUp).Row + 1)


  If IsNumeric(Target) Then
      If Target < 50000 Then
        Worksheets("Sheet3").Cells(lsh3, 1).Value = Target.Value
      Else
        Worksheets("Sheet4").Cells(lsh4, 1).Value = Target.Value
      End If
  Else
    Worksheets("Sheet2").Cells(lsh2, 1).Value = Target.Value
  End If


Cancel = True


End Sub

HTH

 

[Zetroza]

Absolutely wonderful,
I love that everything from the start of this post has evolved to create everything i ever needed and more for my project.

Thank you so much for your help and teachings,

 

[James006]

Absolutely wonderful,
I love that everything from the start of this post has evolved to create everything i ever needed and more for my project.

Thank you so much for your help and teachings,

Glad this could be of assistance ...

Let me sincerely thank you for all your very nice comments

 

[Zetroza]

Has it been that long already?

This code is still continually helping me on a daily basis so once a gain thank you and bravo
Working from the code posted above at "Oct 21st, 2015, 11:39 AM"

What I cannot understand is where the code divides into "isNumeric" and "else" why it will decipher a date as text rather than a number.
This is causing me an issue by sending the targeted numeric cell (which could be a date) to the text worksheet.

Note: that if my target cell is a date it reads as "DD/MM/YY" and may be a result of a formula rather than self input data.
Any ideas or observations would again be greatly appreciated.
Many thanks,

 

[James006]

Hello,

To account for the dates, you could test the following :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
' Text goes to Sheet 2
' Number < 50000 goes to Sheet 3
' Date goes to Sheet 3
' Number >=50000 goes to Sheet 4


If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1:B10")) Is Nothing Then Exit Sub
Dim lsh2 As Long
Dim lsh3 As Long
Dim lsh4 As Long


lsh4 = IIf(Worksheets("Sheet4").Range("A1") = "", 1, Worksheets("Sheet4").Range("a65536").End(xlUp).Row + 1)
lsh3 = IIf(Worksheets("Sheet3").Range("A1") = "", 1, Worksheets("Sheet3").Range("a65536").End(xlUp).Row + 1)
lsh2 = IIf(Worksheets("Sheet2").Range("A1") = "", 1, Worksheets("Sheet2").Range("a65536").End(xlUp).Row + 1)


  If IsDate(Target) Then
    Worksheets("Sheet3").Cells(lsh3, 1).Value = Target.Value
  ElseIf IsNumeric(Target) Then
      If Target < 50000 Then
        Worksheets("Sheet3").Cells(lsh3, 1).Value = Target.Value
      Else
        Worksheets("Sheet4").Cells(lsh4, 1).Value = Target.Value
      End If
  Else
    Worksheets("Sheet2").Cells(lsh2, 1).Value = Target.Value
  End If
Cancel = True
End Sub

Hope this will correspond to your requirements ...

Cheers