PDA

View Full Version : [SOLVED] my old code is strange today



Mergh06
08-08-2015, 07:43 AM
Hello buddies,

My old code, 'synchronize' data. For example I have:

14106


14107


Have a good day, - the code is below:



14108




In text:

Sub ertert122()
Dim x, y(), i&, j&, k, t$, sp
x = Sheets("Raw").Range("A1").CurrentRegion.Value
With CreateObject("Scripting.Dictionary")
.CompareMode = 1
For i = 1 To UBound(x)
If Len(x(i, 1)) Then .Item(x(i, 1) & "|" & x(i, 2)) = x(i, 3)
Next i
For j = 4 To UBound(x, 2) Step 3
For i = 1 To UBound(x, 1)
If Len(x(i, j)) Then
t = x(i, j) & "|" & x(i, j + 1)
If .Exists(t) Then .Item(t) = .Item(t) & "~" & x(i, j + 2)
End If
Next i
Next j: i = 0
ReDim y(1 To .Count, 1 To UBound(x, 2) / 3 + 2)
For Each k In .keys
If InStr(.Item(k), "~") Then
sp = Split(.Item(k), "~")
If UBound(sp) = UBound(x, 2) / 3 - 1 Then
i = i + 1
For j = 0 To UBound(sp): y(i, j + 3) = sp(j): Next j
sp = Split(k, "|"): y(i, 1) = sp(0): y(i, 2) = sp(1)
End If
End If
Next k
End With
If i = 0 Then MsgBox "Oops, there are no matching": Exit Sub
With Sheets("Result").Range("A1")
.CurrentRegion.ClearContents
.Resize(i, UBound(y, 2)).Value = y(): .Parent.Activate
End With
End Sub



- best

Mergh06
08-08-2015, 03:18 PM
14111

I reply my own thread here with a attachment of the problem.
Sheet Raw is the raw data that is matched up.
Sheet Result is the result! :)


As you can see, the result for Cars, Bikes and Cycles differs from the Raw sheet, because of the decimal separator some, destroy everything for me.

-Best

Mergh06
08-08-2015, 10:48 PM
Any help would be greatly appreciated!

Mergh06
08-11-2015, 05:34 AM
Ok after a lot of time I finally found a solution.

For future googlers:

I'm not 100% sure, however i'm pretty confident that the solution lies at language settings within Windows.
I changed the Windows setting 'Current language for non-unicode program' to English. This match my Excel. Gl