Removing Hyphen from number string using VBA

chris pike
chris pike used Ask the Experts™
on
I had another great expert help me with this one, but I know they are too busy and I don't want to bug them on this one.

Why isn't this code working?

I have two kinds of codes being entered by a user:
DD-03-A    ......AND......      DD03A  

Why isn't this VBA working?

ElseIf nColScan = 10 Then
      '151208 s4p remove hypens
      If Len(sValue) < 5 Then
         sValue = Trim(Replace(sValue, "-", "")) 'remove hypens, trim leading and trailing spaces
      End If
      If Len(sValue) <= 7 Then
      nRowNext = nRowScan + nRowDataSkip 

Open in new window


Thanks Experts
Chris
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
Hi,

because you test Len(sValue) < 5

and DD-03-A has a len of 7

Regards

Author

Commented:
I changed the first Len value to 7 and still doesn't work.
Is the  "ElseIf nColScan = 10 Then"   wrong or something?
Thanks
Top Expert 2016

Commented:
then try
ElseIf nColScan = 10 Then
      '151208 s4p remove hypens
      sValue = Trim(Replace(sValue, "-", "")) 'remove hypens, trim leading and trailing spaces
      If Len(sValue) <= 5 Then
      nRowNext = nRowScan + nRowDataSkip 

Open in new window

Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Author

Commented:
Hmmm,
I am Debugging - Compiling, then saving the VBA then saving the Excel wb after pasting the code and still doesnt remove hyphens.
Any Idea?

Thanks
Top Expert 2016

Commented:
is there a difference in the 2  msgboxes

ElseIf nColScan = 10 Then
      '151208 s4p remove hypens
      MsgBox sValue
      sValue = Trim(Replace(sValue, "-", "")) 'remove hypens, trim leading and trailing spaces
      MsgBox sValue
      If Len(sValue) <= 5 Then
      nRowNext = nRowScan + nRowDataSkip

Open in new window

Author

Commented:
HI there,
Here is all the code for that part
 ElseIf nColScan = 10 Then
      '151208 s4p remove hypens
      sValue = Trim(Replace(sValue, "-", "")) 'remove hypens, trim leading and trailing spaces
      If Len(sValue) <= 5 Then
      nRowNext = nRowScan + nRowDataSkip
         
         With wsData
            'color location just scanned pale yellow and lock
            With .Range("J" & nRowScan)
               'if last character is B or C, change to A
'               If UCase(Right(.Value, 1)) = "B" Or UCase(Right(.Value, 1)) = "C" Then

               'if last character is <> "A", change to A
               If UCase(Right(.Value, 1)) <> "A" Then
                  .Value = Left(.Value, Len(.Value) - 1) & "A" '151208 s4p
               End If
               .Interior.Color = RGB(255, 240, 180) 'location is yellow 250,230,200
               .Locked = True
            End With
            'fill in "skid" for the next row
            .Range("A" & nRowNext) = "Skid"
            'increment skid number
            .Range("B" & nRowNext) = wsData.Range("B" & nRowScan) + 1
            'select next Item Number cell and change its color
            With .Range("D" & nRowNext)
Debug.Print nRowNext
               .Interior.Color = RGB(255, 255, 0)  'bright yellow for active cell
               .Locked = False
               .Select
            End With
         End With
      Else
         With Target
            .Locked = False
            .Select
         End With
         MsgBox "Please scan a LOCATION", , "Error: Scan LOCATION"
         GoTo Proc_Exit
      End If
   Else
'      MsgBox "Scan in wrong column (" & nColScan & "), contact Chris", , "Error: Scan in wrong column"
      wsData.Cells(nRowScan, 4).Select
      GoTo Proc_Exit
   End If
    
Proc_Exit:
   On Error Resume Next
   Application.EnableEvents = True
   'put protection back on
   Call ABC

   Set wsData = Nothing
   Exit Sub

Open in new window

Author

Commented:
I am afraid it is a bit out of my reach of understanding.
Thanks for having a look.

I tried just doing a find/replace in the range of the column, but when I ran the macro it messed up the rest of the sheet. I think because the sheet is still running something in the background.
Chris
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Do you have a workbook, or sample, that you can attach?

Author

Commented:
Hi MArtin.
I have to strip the data.
Hang on
Thanks

Author

Commented:
OK Here we go.
This is the long code that you can copy into D4 for the first code
11032640 20OCT2015 J5572 5

Then the cursor moves to J4 waiting for the next short code, us one with a hyphen
DD-10-B
DD13B
DD-02-A
DD04A

Just copy the codes and select D4-Paste and J4 Paste, the rest is automatic.
It changes to B to A, which is great, works fine.

Just doesn't remove the Hyphen.
Thanks
Location-Allocation-test-remove-hyp.xlsm
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Add lines 4 to 6
   ElseIf nColScan = 10 Then
      '151208 s4p remove hypens
      sValue = Trim(Replace(sValue, "-", "")) 'remove hypens, trim leading and trailing spaces
      If Target.Address = "$J$4" Then
            Target = sValue
      End If

Open in new window


I don''t know enough about the code to say so for sure but you may just need line 5 and not 4 and 6.

Author

Commented:
@Martin
Yup Just needed Line 5. Boy.... My fingers were crossed.
I am nervous messing around with code.
But I think I got it.
Still testing.
Chris

Author

Commented:
Good Job, Thanks for the help.
Appreciate it.
Worked well.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.

Marty - MVP 2009 to 2015
              Experts Exchange MVE 2015
              Experts-Exchange Top Expert Visual Basic Classic 2012 to 2015

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial