compare cell and insert vaalue in column Q

[sal21]

http://www.GSSITALY.COM/compare.ZIP

i have this 2 wbook in c:\verifiche\

Note: i open for this work the wbook MASTER, and i would want to
maintain into dir the wbook USER

i would want to compare ad copy value of USER column R, into column R
of MASTER in this mode and with 2 condition (in VBA):

condition number 1)
the value of column R of USER is present into same column R of MASTER,
copy the value of column Q of USER into column Q of MASTER...

condition number 2)
the value of column R of USER is not present into same column R of
MASTER, insert into column Q of MASTER the value "NEW"

Tthat its all...

 

[Leith Ross]

Hello Sal21,

This macro will do what you asked based on the information and
worksheets you provided. If you have any problems, you can reach me by
email at (e-mail address removed).


Code:
--------------------
Public Sub UpdateInvoices()

Dim Found As Boolean
Dim I As Long
Dim J As Long
Dim LastMasterRow As Long
Dim LastUserRow As Long
Dim WksMaster As Excel.Worksheet
Dim WksUser As Excel.Worksheet


Set WksMaster = Excel.Workbooks("Master.xls").Worksheets("Master")
Set WksUser = Excel.Workbooks("User.xls").Worksheets("User")

LastMasterRow = WksMaster.Cells(Rows.Count, "R").End(xlUp).Row
LastUserRow = WksUser.Cells(Rows.Count, "R").End(xlUp).Row

For J = 2 To LastUserRow
For I = 2 To LastMasterRow
If WksMaster.Cells(I, "R") = WksUser.Cells(J, "R") Then
Found = True
Exit For
Else
Found = False
End If
Next I
If Found Then
WksMaster.Cells(I, "Q") = WksUser.Cells(I, "Q")
Else
WksMaster.Cells(I, "Q") = "New"
End If
Next J

End Sub

 

[sal21]

Hello Sal21,

This macro will do what you asked based on the information and
worksheets you provided. If you have any problems, you can reach me by
email at (e-mail address removed).
Code:
--------------------

Dim Found As Boolean
Dim I As Long
Dim J As Long
Dim LastMasterRow As Long
Dim LastUserRow As Long
Dim WksMaster As Excel.Worksheet
Dim WksUser As Excel.Worksheet


Set WksMaster = Excel.Workbooks("Master.xls").Worksheets("Master")
Set WksUser = Excel.Workbooks("User.xls").Worksheets("User")

LastMasterRow = WksMaster.Cells(Rows.Count, "R").End(xlUp).Row
LastUserRow = WksUser.Cells(Rows.Count, "R").End(xlUp).Row

For J = 2 To LastUserRow
For I = 2 To LastMasterRow
If WksMaster.Cells(I, "R") = WksUser.Cells(J, "R") Then
Found = True
Exit For
Else
Found = False
End If
Next I
If Found Then
WksMaster.Cells(I, "Q") = WksUser.Cells(I, "Q")
Else
WksMaster.Cells(I, "Q") = "New"
End If
Next J

End Sub --------------------

Sincerely,
Leith Ross

Hi, Leith Ross
Tks for code. It work well....
But, why not insert the value "new" in other cell of sheet MASTER, when
not exist correpondence from USER to MASTER?
I attache my sheet master with my idea...Your macro put the value new
only in cell 408 of MASTER
Sorry for my english but sure you have understand me, you have posted
me a good code.
Coffe a nd pizza are for you...


+-------------------------------------------------------------------+
|Filename: MASTER.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3937 |
+-------------------------------------------------------------------+

 

[Leith Ross]

Hello Sal21,

This code should put "New" into the Master cell if there is n
correspondce with the User cell or the Master cell has a number, but n
description.


Code
-------------------
Public Sub UpdateInvoices()

Dim LastUserRow As Long
Dim WksMaster As Excel.Worksheet
Dim WksUser As Excel.Worksheet


Set WksMaster = Excel.Workbooks("Master.xls").Worksheets("Master")
Set WksUser = Excel.Workbooks("User.xls").Worksheets("User")

LastMasterRow = WksMaster.Cells(Rows.Count, "R").End(xlUp).Row
LastUserRow = WksUser.Cells(Rows.Count, "R").End(xlUp).Row

For J = 2 To LastUserRow
For I = 2 To LastMasterRow
If WksMaster.Cells(I, "R") = WksUser.Cells(J, "R") Then
WksMaster.Cells(I, "Q") = WksUser.Cells(I, "Q")
Else
WksMaster.Cells(I, "Q") = "New"
End If
If WksMaster.Cells(I, "Q") = "" Then WksMaster.Cells(I, "Q") = "New"
Next I
Next J

End Su
-------------------


By the way, if you have trouble writing something in English, feel fre
to write it in Italian. I am part Italian and can read it pretty well.

Ciao,
Leith Ros