VBA code blocked

[Panoos64]

Hi all, i have the below code, which i was working for many years, but it block in a sheet on the point with blue letters.
I would much appreciated if you could help me so that to run the code. The message which appears is follow:

Run-time error '13':
Type mismatch


Thanks in advance


Dim Rng As Range, Dn As Range, N As Long, nRng As Range
Set Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
If Not .exists(Dn.Value) Then
.Add Dn.Value, Dn
Else
.Item(Dn.Value).Offset(, 1).Value = _
.Item(Dn.Value).Offset(, 1).Value + Dn.Offset(, 1)
.Item(Dn.Value).Offset(, 2).Value = _
.Item(Dn.Value).Offset(, 2).Value + Dn.Offset(, 2)

If nRng Is Nothing Then Set nRng = Dn Else Set nRng = Union(nRng, Dn)
End If
Next
If Not nRng Is Nothing Then nRng.EntireRow.Delete
End With

 

[MickG]

Does your data in column "D" consist of some cells "Text" and some cells "numbers", this would cause the problem !!!

 

[Panoos64]

Hi Mick, please see below an extract of my data. You are right to the above, that the col. "D" consists "Text" and "Numbers" and i tried to change it using the option "Format Cells" but it still not working. I would be greatly appreciated if you could provide me support so that to format the col. "C" & "D" and turn them in "Numbers" Thanks once again for your continued support.
Hv a great day

<colgroup><col style="width:48pt" width="64" span="5"> </colgroup><tbody>
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]A[/TD]
[TD="class: xl63, width: 64"]B[/TD]
[TD="class: xl63, width: 64"]C[/TD]
[TD="class: xl63, width: 64"]D[/TD]

[TD="class: xl63, width: 64"]1[/TD]
[TD="class: xl63, width: 64"]DATE[/TD]
[TD="class: xl63, width: 64"]INV.NO[/TD]
[TD="class: xl63, width: 64"]DEBIT [/TD]
[TD="class: xl63, width: 64"]CREDIT[/TD]

[TD="class: xl63, width: 64"]2[/TD]
[TD="class: xl65, width: 64"]01-05-16[/TD]
[TD="class: xl63, width: 64"]1010102[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]125.36[/TD]

[TD="class: xl63, width: 64"]3[/TD]
[TD="class: xl65, width: 64"]01-05-16[/TD]
[TD="class: xl63, width: 64"]1010105[/TD]
[TD="class: xl63, width: 64"]200.36[/TD]
[TD="class: xl63, width: 64"][/TD]

[TD="class: xl63, width: 64"]4[/TD]
[TD="class: xl65, width: 64"]02-05-16[/TD]
[TD="class: xl63, width: 64"]1010108[/TD]
[TD="class: xl63, width: 64"]101.35[/TD]
[TD="class: xl63, width: 64"][/TD]

[TD="class: xl63, width: 64"]5[/TD]
[TD="class: xl65, width: 64"]03-05-16[/TD]
[TD="class: xl63, width: 64"]1010123[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]98.35[/TD]

[TD="class: xl63, width: 64"]6[/TD]
[TD="class: xl65, width: 64"]04-05-16[/TD]
[TD="class: xl63, width: 64"]2015456[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]478.36[/TD]

[TD="class: xl63, width: 64"]7[/TD]
[TD="class: xl64, align: right"]06-05-16[/TD]
[TD="class: xl63, width: 64"]1010123[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]12.45
[/TD]

[TD="class: xl63, width: 64"]8[/TD]
[TD="class: xl64, align: right"]09-05-16[/TD]
[TD="class: xl63, width: 64"]1010108[/TD]
[TD="class: xl63, width: 64"]58.65[/TD]

</tbody>

 

[MickG]

I have found no problem running the original code on your data except for "1010123", where there are two values with no numbers in column "C", resulting in "0" in column "C" after the code has run.
I have altered the code to eliminate that.
If you still have a problem call back with problem Data.

Altered code:-

[COLOR=navy]Sub[/COLOR] MG26May54
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, N [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] nRng [COLOR=navy]As[/COLOR] Range
 [COLOR=navy]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
 [COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
 .CompareMode = vbTextCompare
 [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]If[/COLOR] Not .exists(Dn.Value) [COLOR=navy]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR=navy]Else[/COLOR]
        [COLOR=navy]If[/COLOR] Not IsEmpty(Dn.Offset(, 1).Value) [COLOR=navy]Then[/COLOR]
            .Item(Dn.Value).Offset(, 1).Value = _
            .Item(Dn.Value).Offset(, 1).Value + Dn.Offset(, 1)
        [COLOR=navy]End[/COLOR] If
        [COLOR=navy]If[/COLOR] Not IsEmpty(Dn.Offset(, 2).Value) [COLOR=navy]Then[/COLOR]
            .Item(Dn.Value).Offset(, 2).Value = _
            .Item(Dn.Value).Offset(, 2).Value + Dn.Offset(, 2)
        [COLOR=navy]End[/COLOR] If
 [COLOR=navy]If[/COLOR] nRng [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR] [COLOR=navy]Set[/COLOR] nRng = Dn Else [COLOR=navy]Set[/COLOR] nRng = Union(nRng, Dn)
 [COLOR=navy]End[/COLOR] If
 [COLOR=navy]Next[/COLOR]
 [COLOR=navy]If[/COLOR] Not nRng [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR] nRng.EntireRow.Delete
 [COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[Panoos64]

Thank u Mick, with your amendment in code, it runs and works right now either the columns contains "text" or "numbers". You support me several times and i express my appreciation. Hv a great, lovely date!