Macro Help!!!

  • Thread starter Thread starter Arran
  • Start date Start date
A

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
 
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.
 
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
 
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...
 
Hi Nigel,

This didn't work either.

Nigel said:
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)
Click to expand...
 
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
 
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?
 
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
 
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
 
Back
Top