Carlos Amaya
New Member
- Joined
- Aug 27, 2020
- Messages
- 4
- Office Version
- 2016
- Platform
- Windows
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!
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!