Including dashes to code

Carlos Amaya

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

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
557
Office Version
  1. 365
Platform
  1. Windows
Will it mess things up to remove the dashes when comparing? SUBSTITUTE "" for "-".
 

Bill Williamson

Board Regular
Joined
Oct 7, 2019
Messages
124
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
 

Carlos Amaya

New Member
Joined
Aug 27, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

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

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

Watch MrExcel Video

Forum statistics

Threads
1,127,847
Messages
5,627,239
Members
416,232
Latest member
Ash1432

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top