New error with TRIM function

KatKitKat

New Member
Joined
Apr 27, 2022
Messages
33
Office Version
  1. 365
Platform
  1. Windows
Hello everyone.

I have been getting a VBA error message in an Excel file that has been in use for a few months with no problems until now. There have been no changes to the formulas or coding and the cells which are impacted by the VBA code is locked so no one can enter incorrect info or change the data list.

I have an excel file where the user makes a selection from a drop-down list in Cell F. The options are a string with a 5 or 6 character number, followed by a hyphen then text (e.g., "00890-Concrete"). The below code trimmed everything from the hyphen to the right of the selection so only the number appears in the cell ("00890"). This is important because there are other cells in the spreadsheet that populate data via a VLOOKUP formula based on the number only.

This formula has been working fine until yesterday. We now receive a "run time error 13" message when the user goes into any of the cells with this drop-down field and when I debug, the "strInput = Trim(Mid(Target.Value, 1, Application.Find("-", Target.Value) - 1))" line is highlighted in the VBA coding.

I am unsure if my company did a Windows update and that is why this is happening? I tried replacing the hyphen with an equal sign or blank space and still have the run time error. Anyone have a clue why this would happen? Is the TRIM function now obsolete?

Any guidance would be greatly appreciated.
Kat

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim strInput As String
 
   'column F
   If Target.Column <> 6 Then Exit Sub
   If Target.Value = "" Then Exit Sub
 
   'find the hyphen
      strInput = Trim(Mid(Target.Value, 1, Application.Find("-", Target.Value) - 1))
 
   'disable events before writing to worksheet
   On Error Resume Next
   Application.EnableEvents = False
      Target.Value = strInput
   Application.EnableEvents = True
End Sub
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
You will get that error if the entry does not contain a hyphen.

Here is a little better way to do it:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
 
   Dim arr() As String
 
   'column F
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column <> 6 Then Exit Sub
   If Target.Value = "" Then Exit Sub
 
   'check for hyphen the hyphen
   If InStr(1, Target, "-") > 0 Then
      arr = Split(Target, "-")
      'disable events before writing to worksheet
      Application.EnableEvents = False
      Target.Value = Trim(arr(1))
      Application.EnableEvents = True
   End If
 
End Sub
If it does not find a hyphen, it will do nothing.
 
Upvote 0
Thank you for your quick reply, Joe.

This almost worked. There is no longer a run time error, but the trim worked the wrong way. For example:

If the option selected is "00890-Concrete", I need "00890" to appear in the cell. As is, "Concrete" appears in the cell. I am unsure how to change it so I get the number and not the text to populate. This coding is new to me. Could you provide some feedback as to what to change?

Thanks
Kat
 
Upvote 0
Whoops, arr(1) returns the part after, arr(0) would return the part before.
But we also need to pre-format the cell to text to not lose the leading zeroes.

Try this:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
 
   Dim arr() As String
 
   'column F
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column <> 6 Then Exit Sub
   If Target.Value = "" Then Exit Sub
 
   'check for hyphen the hyphen
   If InStr(1, Target, "-") > 0 Then
      arr = Split(Target, "-")
      'disable events before writing to worksheet
      Application.EnableEvents = False
      Target.NumberFormat = "@"
      Target.Value = Trim(arr(0))
      Application.EnableEvents = True
   End If
 
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top