Removing Hyphen from number string using VBA

Hi,

because you test Len(sValue) < 5

and DD-03-A has a len of 7

Regards

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

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 

Select allOpen in new window

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

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

Select allOpen in new window

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

Select allOpen in new window

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

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

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

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

Good Job, Thanks for the help.
Appreciate it.
Worked well.

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