Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare cell and insert vaalue in column Q
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... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare cell and insert vaalue in column Q
Leith Ross Wrote: 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 . 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 -------------------- 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 | +-------------------------------------------------------------------+ -- sal21 ------------------------------------------------------------------------ sal21's Profile: http://www.excelforum.com/member.php...fo&userid=2040 View this thread: http://www.excelforum.com/showthread...hreadid=478329 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare cell and insert vaalue in column Q
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 -- Leith Ros ----------------------------------------------------------------------- Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846 View this thread: http://www.excelforum.com/showthread.php?threadid=47832 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maddening Dilemma - Compare each cell within column a to each cell in column b and find unique matches | Excel Worksheet Functions | |||
compare cells in column to criteria, then average next column cell | Excel Worksheet Functions | |||
Compare text string of a cell in Column A VS another cell in Colum | Excel Discussion (Misc queries) | |||
Compare text string of a cell in Column A VS another cell in Colum | Excel Worksheet Functions | |||
Need a insert a empty cell if compare different function | Excel Discussion (Misc queries) |