Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
546 views
in Technique[技术] by (71.8m points)

Why can't this VBA Excel code read duplicates?

    r = 11

Do While Not tgtWSheet.Cells(r, 2) = "0"

    If tgtWSheet.Cells(r, 2) = tgtWSheet.Cells(r + 1, 2) Or tgtWSheet.Cells(r, 2) = tgtWSheet.Cells(r + 2, 2) Or tgtWSheet.Cells(r, 2) = tgtWSheet.Cells(r + 3, 2) Or tgtWSheet.Cells(r, 2) = tgtWSheet.Cells(r + 4, 2) Or tgtWSheet.Cells(r, 2) = tgtWSheet.Cells(r + 5, 2) Or tgtWSheet.Cells(r, 2) = tgtWSheet.Cells(r + 6, 2) Or tgtWSheet.Cells(r, 2) = tgtWSheet.Cells(r + 7, 2) Or tgtWSheet.Cells(r, 2) = tgtWSheet.Cells(r + 8, 2) Or tgtWSheet.Cells(r, 2) = tgtWSheet.Cells(r + 9, 2) Or tgtWSheet.Cells(r, 2) = tgtWSheet.Cells(r + 10, 2) Or tgtWSheet.Cells(r, 2) = tgtWSheet.Cells(r + 11, 2) Or tgtWSheet.Cells(r, 2) = tgtWSheet.Cells(r + 12, 2) Or tgtWSheet.Cells(r, 2) = tgtWSheet.Cells(r + 13, 2) Or tgtWSheet.Cells(r, 2) = tgtWSheet.Cells(r + 14, 2) Or tgtWSheet.Cells(r, 2) = tgtWSheet.Cells(r + 15, 2) Or tgtWSheet.Cells(r, 2) = tgtWSheet.Cells(r + 16, 2) Or tgtWSheet.Cells(r, 2) = tgtWSheet.Cells(r + 17, 2) Or tgtWSheet.Cells(r, 2) = tgtWSheet.Cells(r + 18, 2) Then

        MsgBox "Duplicate Record Found!"
        Exit Sub

    Else

        r = r + 1

    End If

Loop
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

I think you should try "And" instead of "Or". The reason is because of a logic issue. I think someone has asked this question before. You can refer to the following post for some help.

check a record for Duplicates Records, Before Creating New Records

Anyway, his chain of thought is to give a name to the column and check if there is any duplicate in the row using the following formula.

rst.FindFirst "[ID] <> " & Me.ID & _
 " AND [TitleText] = '" & Me.TitleText & _
 "' AND [UnitCode] = " & Me.UnitCode & _
 " AND [AcademicYear] = " & Me.AcademicYear & _
 " AND [Titleofchapterjournalarticle] = '" & Me.Titleofchapterjournalarticle & "'"

Hope it helps.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...