Run-time ERROR 13 Type Mismatch

[bazwillrun]

Yes, that old chestnut....
Excel 2013
Been using this macro for around 7 years on a membership database with no problems whatsoever. Checks data in "current" sheet and adds/changes to corresponding cells in "archive" sheet
Yesterday as usual added a couple of new members to "current" ran macro it kept hanging with the error message as posted.

Databas has 23 cols and 1800 rows.

macro and highlighted line that throws up error below.

Have googled the problem but its not helped me and looked for any obvious issues on database but coming up with nothing.

any help or pointers greatly appreciated...im not a programmer so go easy on me !

thanks.

Sub synchronize()
ActiveSheet.DisplayPageBreaks = False
Dim Rng1 As Range, Dn1 As Range, Rng2 As Range, Dn2 As Range
With Sheets("Current") '1
Set Rng1 = .Range(.Range("D1"), .Range("D" & Rows.Count).End(xlUp))
End With

With Sheets("Archive") '2
Set Rng2 = .Range(.Range("D1"), .Range("D" & Rows.Count).End(xlUp))
End With
For Each Dn2 In Rng2
For Each Dn1 In Rng1
If UCase(Trim(Dn2)) = UCase(Trim(Dn1)) Then
Dn1.Offset(, -3).Resize(, 23).Copy Dn2.Offset(, -3).Resize(, 23)
End If
Next Dn1
Next Dn2
End Sub

 

[steve the fish]

Almost certainly you have an error cell in your range/s

 

[wideboydixon]

When it stops on that line, press [Ctrl]+G to bring up the immediate window and type the following (hitting [Enter] after each line):

? Dn2.Address
? Dn1.Address
? Dn2.Value
? Dn1.Value

Post the responses here.

WBD

 

[Norie]

Are there are any error values in column D of either sheet?

Are thee any merge cells in those columns?

By the way, what is the code mean to do?

Do you just want to copy from 'Current' to 'Archive' when there's a matching value in column D?

If that's the case there's no real need for two loops, you could loop down column D on 'Current', check if there's a match in column D on 'Archive' using Application.Match and if there is copy.

 

[wideboydixon]

Hey @Norie,

I thought the same but the UCase(Trim()) scared me; it suggests there could be case differences and leading/trailing spaces that would foil traditional attempts. If I was going to speed this up, I'd probably look at using a Dictionary.

WBD

 

[bazwillrun]

When it stops on that line, press [Ctrl]+G to bring up the immediate window and type the following (hitting [Enter] after each line):

? Dn2.Address
? Dn1.Address
? Dn2.Value
? Dn1.Value

Post the responses here.

WBD

Many thanks, appreciate the help.

Ok, this is what I got assuming I did it correctly.
C & P from the "immediate" window that opened..

? Dn2.Address
$D$235

? Dn1.Address
$D$1

? Dn2.Value
Error 2023
? Dn1.Value
Mem No

 

[bazwillrun]

@ Norie

Theres none that I can see, nothing shows with excel built in error flags.
no merges that im aware of, been using this macro as is and no changes except cell data for years without problems.
looks at four digit membership number in "archive" and compares against same number in "current" and any differences i n"current" it copies or adds on"archive"..if that makes any sense

 

[Norie]

What about the error value in D235 on 'Archive'?

 

[bazwillrun]

not sure how but , yes, in archive D235 now showed an error..
cleared all ,data, formats and re-entered and macro now working fine again.

Many thanks for all you speedy and spot on help and advice.

 

[Norie]

No problem, to avoid the same thing happening you could try this minor change.

If UCase(Trim(Dn2.Text)) = UCase(Trim(Dn1.Text)) Then