Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy data to next available row
To all,
I have the following code that is working well and copies information from 1 sheet to another, but what I want to change it slightly so that if the value in DBWks.Cells(MLRow, 3) matches that in NewFormWks.Cells(7, 4) a message box pops up and stats that the record is duplicated. if this is the case the sub should be exited. Can anyone help?? 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 DBWks.Cells(MLRow, 2).Value = NewFormWks.Cells(5, 7).Value 'Division DBWks.Cells(MLRow, 3).Value = NewFormWks.Cells(7, 4).Value 'Contract No DBWks.Cells(MLRow, 4).Value = NewFormWks.Cells(7, 7).Value 'Branch 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 DBWks.Cells(MLRow, 4).Value = NewFormWks.Cells(7, 7).Value 'Branch End If Loop |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy data to next available row
Just a guess but see if this does what you want.
MLRow = 4 'MasterList Start Row Do Until ThisWorkbook.Sheets("Data Base").Cells(MLRow, 3) = "" With DBWks If .Cells(MLRow, 3).Value = _ NewFormWks.Cells(7, 4).Value Then msg = MsgBox(.Cells(MLRow, 3).Value & Chr(10) & _ "Duplicated Record", 16, "Copy Data") Exit Sub ElseIf .Cells(MLRow, 3).Value = "" Then 'Division .Cells(MLRow, 2).Value = _ NewFormWks.Cells(5, 7).Value 'Contract No .Cells(MLRow, 3).Value = _ NewFormWks.Cells(7, 4).Value 'Branch .Cells(MLRow, 4).Value = _ NewFormWks.Cells(7, 7).Value End If End With MLRow = MLRow + 1 Loop -- jb "Arran" wrote: To all, I have the following code that is working well and copies information from 1 sheet to another, but what I want to change it slightly so that if the value in DBWks.Cells(MLRow, 3) matches that in NewFormWks.Cells(7, 4) a message box pops up and stats that the record is duplicated. if this is the case the sub should be exited. Can anyone help?? 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 DBWks.Cells(MLRow, 2).Value = NewFormWks.Cells(5, 7).Value 'Division DBWks.Cells(MLRow, 3).Value = NewFormWks.Cells(7, 4).Value 'Contract No DBWks.Cells(MLRow, 4).Value = NewFormWks.Cells(7, 7).Value 'Branch 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 DBWks.Cells(MLRow, 4).Value = NewFormWks.Cells(7, 7).Value 'Branch End If Loop |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy data to next available row
Hi John, thanks for the reply, but I am sorry to say that it only partially
worked. If the record was already in the data base then the message popped up fine but, if the record wasn't in the database it didn't add it in. Any ideas? "john" wrote: Just a guess but see if this does what you want. MLRow = 4 'MasterList Start Row Do Until ThisWorkbook.Sheets("Data Base").Cells(MLRow, 3) = "" With DBWks If .Cells(MLRow, 3).Value = _ NewFormWks.Cells(7, 4).Value Then msg = MsgBox(.Cells(MLRow, 3).Value & Chr(10) & _ "Duplicated Record", 16, "Copy Data") Exit Sub ElseIf .Cells(MLRow, 3).Value = "" Then 'Division .Cells(MLRow, 2).Value = _ NewFormWks.Cells(5, 7).Value 'Contract No .Cells(MLRow, 3).Value = _ NewFormWks.Cells(7, 4).Value 'Branch .Cells(MLRow, 4).Value = _ NewFormWks.Cells(7, 7).Value End If End With MLRow = MLRow + 1 Loop -- jb "Arran" wrote: To all, I have the following code that is working well and copies information from 1 sheet to another, but what I want to change it slightly so that if the value in DBWks.Cells(MLRow, 3) matches that in NewFormWks.Cells(7, 4) a message box pops up and stats that the record is duplicated. if this is the case the sub should be exited. Can anyone help?? 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 DBWks.Cells(MLRow, 2).Value = NewFormWks.Cells(5, 7).Value 'Division DBWks.Cells(MLRow, 3).Value = NewFormWks.Cells(7, 4).Value 'Contract No DBWks.Cells(MLRow, 4).Value = NewFormWks.Cells(7, 7).Value 'Branch 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 DBWks.Cells(MLRow, 4).Value = NewFormWks.Cells(7, 7).Value 'Branch End If Loop |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy data when data is detected in another column(s). | Excel Worksheet Functions | |||
Cyclic Data Macro Q | Free Copy Macro | Excel Programming | |||
Copy data in a macro. | Excel Discussion (Misc queries) | |||
Macro to copy data | Excel Programming | |||
Macro to copy data | Excel Programming |