| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help!!!
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Help!!!
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" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Help!!!
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 "Joel" wrote: 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" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Help!!!
Hi Nigel,
This didn't work either. "Nigel" wrote: 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 "Arran" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Help!!!
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" wrote: Hi Nigel, This didn't work either. "Nigel" wrote: 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 "Arran" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Help!!!
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? "Joel" wrote: 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" wrote: Hi Nigel, This didn't work either. "Nigel" wrote: 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 "Arran" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Help!!!
Arran,
Ive 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 -- jb "Arran" wrote: 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? "Joel" wrote: 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" wrote: Hi Nigel, This didn't work either. "Nigel" wrote: 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 "Arran" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| AutoRun Macro with a delay to give user the choice to cancel the macro | Excel Programming | |||
| Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
| Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
| how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
| Start Macro / Stop Macro / Restart Macro | Excel Programming | |||
Hybrid Mode
