msgBox help

[Soniya]

Hi All,

in the following code if any ites are repeated they are
shown in the message box. Bu they appea repeated.

How can I avoid appearing the same number again in the
message bos?

TIA
Soniya


Sub Check_Docs4Duplicate()
Dim rng As Range
Dim lookup_rng As Range
Dim cell As Range
Dim msg_str
Dim wks_1 As Worksheet

Set wks_1 = Sheets("FormsData")

LR = Range("B" & Rows.Count).End(xlUp)
Set lookup_rng = wks_1.Range("B:B")
msg_str = ""
Set rng = wks_1.Range("B2:B" & LR)
For Each cell In rng
If cell.Value <> "" Then
If Application.WorksheetFunction.CountIf(lookup_rng,
cell.Value) > 1 Then
If msg_str = "" Then
msg_str = cell.Value
Else
msg_str = msg_str & ", " & cell.Value
End If
End If
End If
Next
If msg_str = "" Then
MsgBox "No DUPLICATE Documents FOUND"

Else
MsgBox "Documents " & msg_str & Chr(13) & "APPEARS MORE
THAN ONCE" _
& ". Documents not UPDATED!"

End If
End Sub

 

[Frank Kabel]

Hi
try replacing the line
If Application.WorksheetFunction.CountIf(lookup_rng,
cell.Value) > 1 Then

with
If Application.WorksheetFunction.CountIf(lookup_rng,
cell.Value) = 2 Then

 

[Kalle]

test wheter the item is already in msgstr.

use Instr(yourMsg, NewValue)=0

 

[Tom Ogilvy]

Sub Check_Docs4Duplicate()
Dim rng As Range
Dim lookup_rng As Range
Dim cell As Range
Dim msg_str
Dim wks_1 As Worksheet

Set wks_1 = Sheets("FormsData")

LR = Range("B" & Rows.Count).End(xlUp).Row
msg_str = ""
Set rng = wks_1.Range("B2:B" & LR)
For Each cell In rng
Set lookup_rng = wks_1.Range(rng(1), cell)
If cell.Value <> "" Then
If Application.WorksheetFunction.CountIf(lookup_rng, _
cell.Value) = 2 Then
If msg_str = "" Then
msg_str = cell.Value
Else
msg_str = msg_str & ", " & cell.Value
End If
End If
End If
Next
If msg_str = "" Then
MsgBox "No DUPLICATE Documents FOUND"

Else
MsgBox "Documents " & msg_str & Chr(13) & _
"APPEARS MORE THAN ONCE" _
& ". Documents not UPDATED!"

End If
End Sub


Does what you want I believe.