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

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

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