Removing Hyphen from number string using VBA

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
chris pikeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

because you test Len(sValue) < 5

and DD-03-A has a len of 7

Regards
chris pikeAuthor Commented:
I changed the first Len value to 7 and still doesn't work.
Is the  "ElseIf nColScan = 10 Then"   wrong or something?
Thanks
Rgonzo1971Commented:
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

Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

chris pikeAuthor 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
Rgonzo1971Commented:
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

chris pikeAuthor 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

chris pikeAuthor 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 dirtCommented:
Do you have a workbook, or sample, that you can attach?
chris pikeAuthor Commented:
Hi MArtin.
I have to strip the data.
Hang on
Thanks
chris pikeAuthor 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
Martin LissOlder than dirtCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chris pikeAuthor 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
chris pikeAuthor Commented:
Good Job, Thanks for the help.
Appreciate it.
Worked well.
Martin LissOlder than dirtCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.