Macro Help!!!

[Arran]

Hi to all,

I have got some code that copies cells from one sheet to another, if there
is a duplicate a message pops up stating that there is a duplicate and exits
the macro, my problem is that if the data isn't duplicated, the message box
still pops up:

MLRow = 4 'MasterList Start Row

Do Until ThisWorkbook.Sheets("Data Base").Cells(MLRow, 3) = ""
If DBWks.Cells(MLRow, 3).Value = NewFormWks.Cells(7, 4).Value Then
MsgBox ("Duplicated Record: " & vbNewLine & vbNewLine & "Contract
Number: " & _
NewFormWks.Cells(7, 4).Value & " already exists")
NewFormWkbk.Close
Exit Sub
MLRow = 5
Exit Do
End If
MLRow = MLRow + 1

If DBWks.Cells(MLRow, 3).Value = "" Then
DBWks.Cells(MLRow, 2).Value = NewFormWks.Cells(5, 7).Value
'Division
DBWks.Cells(MLRow, 3).Value = NewFormWks.Cells(7, 4).Value
'Contract No

End If
Loop

How can I stop this from happening? Any help will be welcome.

Thanks in advance

 

[Joel]

The data must match!!!! Look at the message closely and see what the the
data that is displayed in the message box. You may be comparing 2 blank
cells which will allow the message box to display.

 

[Arran]

Hi Joel,

thanks for the response but this isn't the case.

the value in NewFormWks.Cells(7, 4). will always be greater than nothing

 

[Nigel]

More often than not when matching cell and you get false matches it is
spaces in the string, try trimming the values first.

Example.....

If Trim(cell) = Trim(cell) then

etc...

 

[Arran]

Hi Nigel,

This didn't work either.

More often than not when matching cell and you get false matches it is
spaces in the string, try trimming the values first.

Example.....

If Trim(cell) = Trim(cell) then

etc...

--

Regards,
Nigel
(e-mail address removed)

 

[Joel]

Add both cell info to message box. I klnow I'm right and you are wrong. The
cells must match!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Do Until ThisWorkbook.Sheets("Data Base").Cells(MLRow, 3) = ""
if DBWks.Cells(MLRow, 3).Value = NewFormWks.Cells(7, 4).Value Then
msgbox("Comparing cell : " & DBWks.Cells(MLRow, 3).Value & vbcrlf _
"Duplicated Record: " & vbNewLine & vbNewLine & _
"Contract Number: " & NewFormWks.Cells(7, 4).Value & " already
exists")
NewFormWkbk.Close
Exit Sub
MLRow = 5
Exit Do
End If
MLRow = MLRow + 1

If DBWks.Cells(MLRow, 3).Value = "" Then
DBWks.Cells(MLRow, 2).Value = _
NewFormWks.Cells(5, 7).Value 'Division
DBWks.Cells(MLRow, 3).Value = _
NewFormWks.Cells(7, 4).Value 'Contract No

End If
Loop

 

[Arran]

Joel,

I don't think that it is a case of the cells don't match, I think it is a
problem with the loop.

I think that when the macro runs, the msgbox is skipped when a new record is
added, it adds the record, then the loop start again and that's when the msg
box comes up because the record now exists.

Does that make sense?

 

[john]

Arran,
I’ve seen this post before – Joels comments are valid data must match & you
probably are comparing two blank cells.

Having scratch my head trying to puzzle out your code construct I conclude
(I think) what you are trying to do is cycle through cells on worksheet DBWks
– if you get a match with NewFormWks.Cells(7, 4) then you report it to user
via msgbox & exit sub?

However, if after testing all populated cells in the range you arrive at the
next blank row in the range, you then add data for division & contract No???

If I have interpreted what you are trying to do then maybe this approach
will work – if not, then perhaps someone else can offer a solution!

MLRow = 4 'MasterList Start Row

Do Until ThisWorkbook.Sheets("Data Base").Cells(MLRow, 3) = ""
If DBWks.Cells(MLRow, 3).Value = _
NewFormWks.Cells(7, 4).Value Then

MsgBox ("Duplicated Record: " & _
vbNewLine & vbNewLine & _
"Contract Number: " & _
NewFormWks.Cells(7, 4).Value & _
" already exists")



GoTo exitprog

End If

MLRow = MLRow + 1

Loop

'Division
DBWks.Cells(MLRow, 2).Value = _
NewFormWks.Cells(5, 7).Value

'Contract No
DBWks.Cells(MLRow, 3).Value = _
NewFormWks.Cells(7, 4).Value

exitprog:

NewFormWkbk.Close

 

[Joel]

I think you are using events like a worksheet change event. You need to
disable the event

application.enableevents = False

then re-enable at end of code

application.enableevents = True