PDA

View Full Version : Problem on Merging



parscon
04-04-2012, 10:23 PM
I have a problem when use this macro , when run this macro on data in sheet show me this error and when click on debug show this line


If a(i, ii) <> "" Then


Run-time error '13':

Type mismatch




Option Explicit
Sub Merging_Duplicate()

Dim a, i As Long, ii As Long, n As Long, z As String, x As Long
a = Sheets("Sheet1").Range("a1").CurrentRegion.Value
n = 1
With CreateObject("Scripting.Dictionary")
.CompareMode = 1
For i = 2 To UBound(a, 1)
For ii = 1 To 1
z = z & Chr(2) & a(i, ii)
Next
If Not .exists(z) Then
n = n + 1: .Item(z) = n
For ii = 1 To UBound(a, 2)
a(n, ii) = a(i, ii)
Next
Else
x = .Item(z)
For ii = 2 To UBound(a, 2)
If a(i, ii) <> "" Then
a(x, ii) = a(x, ii) & IIf(a(x, ii) <> "", ", ", "") & a(i, ii)
End If
Next
End If
z = ""
Next
End With
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Results").Delete
On Error GoTo 0
Sheets.Add().Name = "Results"
With Sheets("Results").Cells(1).Resize(n, UBound(a, 2))
.Value = a
End With
End Sub

Also I attached The workBook .

I use Excel 2010 x64 and windows 7 x64

parscon
04-04-2012, 11:19 PM
I found the problem , when i paste as text , i have this error but when i paste normal it is will not give me any error , how can fix it !!

Thank you .

p45cal
04-04-2012, 11:30 PM
At that point, i=2097 and ii=11, which in a() is an error, not a string. It's from cell K2097.
The same will happen at K9544. Both these cells start with an '=' character.

I'm not sure what you're tryng to do, but a(2) has huge strings in it at the end of the macro.

parscon
04-04-2012, 11:44 PM
This code will be merge duplicate data .

p45cal
04-04-2012, 11:49 PM
This code will be merge duplicate data .
That's not really much more info than the name of the macro.

parscon
04-04-2012, 11:59 PM
If you check this workbook , it work without any error , because i use normal copy and paste , it is not paste as text .

mancubus
04-05-2012, 02:30 AM
i know that code from almost every excel site i visited.
(credits to original author)

that is for transferring/copying unique data.

answer:
if you dont have any problem with "normal"(!) copy paste, why not use it.

if you have problems using "paste as text" (whatever it is) why use it?
is it from web?

and as pascal says, be more specific. what do you mean by "merge duplicate"?

parscon
04-05-2012, 06:51 AM
Yes , i copy from a website and it will take so much time and also it will be very slow but paste as text will be very faster .

Thank you .

mancubus
04-05-2012, 11:20 PM
perhaps importing data directly from related web page will do it.