Reputation: 7107
So the below code works sometimes and sometimes it adds the ampersand (to the copy buffer) which I am trying to cut out of the text string.
The point of the code is to copy the string from the right position up to before the ampersand. However, in random scenarios it still adds the ampersand.
Private Sub Block1_Enter()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim MyData As New DataObject
Dim this As String
Dim oldxt As String
oldtxt = Block1.Text
If InStr(Block1.Text, "&") > 0 Then
this = Trim(Right(Block1.Text, InStr(Block1.Text, "&") - 1))
Block1.Text = "End Date Copied" & this
MyData.SetText this
MyData.PutInClipboard
Application.Wait (Now + #12:00:02 AM#)
Block1.Text = oldtxt
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
can someone try and explain why it sometimes works and sometimes doesnt? Below is an image with the two examples of where it doesnt work correctly
(Note for clarification: the textbox values shown below are the source values, not the results)
Upvotes: 2
Views: 203
Reputation: 17637
Use Mid$()
instead:
this = Trim$(Mid$(Block1.Text, InStr(Block1.Text, "&") + 1))
Upvotes: 3
Reputation: 4514
You're currently getting the position of &
from the left of the string and then using this number to take the date from the right of the string; instead you should subtract the Instr
value of &
from the Len
of the string in order to get your remaining characters. Try this:
Private Sub Block1_Enter()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim MyData As New DataObject
Dim this As String
Dim oldxt As String
oldtxt = Block1.Text
If InStr(Block1.Text, "&") > 0 Then
this = Trim(Right(Block1.Text, (Len(Block1) - InStr(Block1.Text, "&") - 1))
Block1.Text = "End Date Copied" & this
MyData.SetText this
MyData.PutInClipboard
Application.Wait (Now + #12:00:02 AM#)
Block1.Text = oldtxt
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
The reason those dates in particular were errors was because the character length of the second date was shorter than the character length of the first date.
Upvotes: 1