Doug Coats
Doug Coats

Reputation: 7107

Trim Issue With VBA

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)

enter image description here

Upvotes: 2

Views: 203

Answers (2)

SierraOscar
SierraOscar

Reputation: 17637

Use Mid$() instead:

this = Trim$(Mid$(Block1.Text, InStr(Block1.Text, "&") +  1))

Upvotes: 3

Jordan
Jordan

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

Related Questions