Including dashes to code

[Carlos Amaya]

Hi all,

I'm analyzing information I got from the WEB about some products we manage.
My intention is to contrast WEB prices vs prices appearing in our list using a vlookup formula. The issue is that sometimes product keys were not written properly in the WEB. For example our product key may be product123-4 and in the WEB it appears only as product1234. That is, there's a missing dash ("-").

I found all errors were caused because the position of the dash in the code was wrong or there was no dash at all.
Unfortunately not all product keys use a dash in the same position. There may be some codes that require up to five dashes for example: 23056-CA-M-K-E4-C3.

I did this code to include 3 dashes in the key code, however, it's getting too complex and I wonder if there's a better way to do it.

'3 Dashes Code
Sub Dash_3_Code()
Dim d3 As Integer, d2 As Integer, d1 As Integer
Dim c5 As Integer, c4 As Integer, c3 As Integer, c2 As Integer
Dim h4 As String, h3 As String, h2 As String, h1 As String

'If lenght of code is <4 then nothing is done

a = Range("h2").Offset(i, -3).Value
'myLength = Range("h2").Offset(i, -3).Value + 1
myCurrentValue = myOriginalText

'Removing any dashes put in previous step
'myValueWithoutDashes = Replace(myCurrentValue, "-", "")

'myCurrentValue = myValueWithoutDashes

myLength = Len(myCurrentValue)
If myLength < 4 Then
'No long enough to put 3 dashes
Else
For c5 = 1 To myLength - 3
d1 = c5 'position of 1st dash
For c3 = d1 To myLength - 2
d2 = d1 + 1 + c4 'position of 2nd dash
For c2 = d2 To myLength - 1
d3 = 1 + c2 'position of 3rd dash
h1 = Left(myCurrentValue, d1) '1st house text
h2 = Mid(myCurrentValue, d1 + 1, d2 - d1) '2nd house text
h3 = Mid(myCurrentValue, d2 + 1, d3 - d2) '3rd house text
h4 = Mid(myCurrentValue, d3 + 1, myLength - 1) '4th house text
myNewValue = h1 & "-" & h2 & "-" & h3 & "-" & h4
Range("h2").Offset(i, -5).Value = myNewValue 'Setting the new code to Cell

If Range("h2").Offset(i, 0) = "NO" Then 'validating if error persist
c2 = myLength - 1 'If error was solved we close this loop
c3 = myLength - 2
c5 = myLength - 3
Else 'Not solved, loop continues
End If
Next c2
c4 = c4 + 1
c2 = 0
Next c3
d2 = 0
c3 = 0
c4 = 0
Next c5
End If
End Sub

Thanks!

 

[C Moore]

Will it mess things up to remove the dashes when comparing? SUBSTITUTE "" for "-".

 

[Bill Williamson]

I am really new at VBA programming my self, But may have a suggestion. If the problem is always from the Dashes in the data, could you not simply do a find and replace on the part numbers?
When excel is sorting data, it will include - and _ in the sort, But I believe if you replace " - " with "^" Then excel will ignore the ^ for the sort. you could later add them back in.... Just a couple cents worth if an Idea.


Good Luck.

Bill

 

[Norie]

Why not create a new column that uses SUBSTITUTE to remove the dashes?

You could then use that column in any formulas.

 

[Carlos Amaya]

Will it mess things up to remove the dashes when comparing? SUBSTITUTE "" for "-".

Thank you! I over complicated everything by using all that code. Your solution worked perfectly! Thanks!

 

[Carlos Amaya]

I am really new at VBA programming my self, But may have a suggestion. If the problem is always from the Dashes in the data, could you not simply do a find and replace on the part numbers?
When excel is sorting data, it will include - and _ in the sort, But I believe if you replace " - " with "^" Then excel will ignore the ^ for the sort. you could later add them back in.... Just a couple cents worth if an Idea.


Good Luck.

Bill

Thank you! I over complicated everything by using all that code. Your solution worked perfectly! Thanks!

 

[Carlos Amaya]

Why not create a new column that uses SUBSTITUTE to remove the dashes?

You could then use that column in any formulas.

Thank you! I over complicated everything by using all that code. Your solution worked perfectly! Most times simple is best. Thanks!