Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Search two column value are match
Hi All
I have two excel worksheet One is Updated by local user , A worksheet One is Updated by global user. B WorkSheet When A worksheet completed, the Upload to B worksheet. How to check two column value are matched by single Find command ? Provide the ID and Item are matched. Sub UPDATE() Const GPD_RBO_COL = 2 Const GPD_ID_ADD = 5 Const GPD_ITEM_ADD = 4 Const GPD_DT_FMD_COMP_ADD = 10 Const GPD_STATUS = 19 '~~ For Master Workbook Const GP_Sheet = "Digital Item Conversion 012709r" Const GP_RBO_COL = 1 ' Update Field Const GP_ID_RANGE = "V:V" Const GP_ID_COL = 22 Const GP_ITEM_RANGE = "G:G" Const GP_ITEM_COL = 7 Const GP_MANF_SYS = "J:J" Const GP_MANF_SYS_COL = 10 Const GP_DT_FMT_COMP_ADD = "U" Const GP_STATUS_ADD = "Y" Const GP_ModifyDate_COL = "AK" Dim iRows As Long Dim iCols As Long Dim ir, ic As Long Dim iNum As Long Dim wksName As String Dim wkbook As Workbook Dim GPD_ID_VAL As String Dim GPD_ITEM_VAL As String Dim GPD_RBO_VAL As String Dim GPD_DT_FMT_COMP_VAL As Date Dim GPD_STATUS_VAL As String Dim Rng_ID As Range Dim Rng_ITEM As Range Dim Rng_SYSTEM As Range Dim LastRow As Integer Dim Chk_system As Boolean wksName = "Digital_Item_Conversion.xls" Set wkbook = Workbooks(wksName) If wkbook Is Nothing Then MsgBox "Workbook not open " & wksName Exit Sub End If If Application.Selection Is Nothing Then MsgBox "No Open Worksheet", vbCritical Exit Sub End If iNum = MsgBox("Update to ..." & wksName, vbYesNo + vbQuestion) If iNum = vbNo Then Exit Sub iRows = Application.Selection.Rows.Count iCols = Application.Selection.Columns.Count For ir = 1 To iRows GPD_ID_VAL = Trim(Application.Selection.Item(ir, GPD_ID_ADD).Value) If Left(GPD_ID_VAL, 1) = "`" Then GPD_ID_VAL = Mid(GPD_ID_VAL, 2, Len(GPD_ID_VAL)) End If GPD_ITEM_VAL = Trim(Application.Selection.Item(ir, GPD_ITEM_ADD).Value) GPD_RBO_VAL = Trim(Application.Selection.Item(ir, GPD_RBO_COL).Value) GPD_DT_FMT_COMP_VAL = Application.Selection.Item(ir, GPD_DT_FMD_COMP_ADD).Value GPD_STATUS_VAL = Application.Selection.Item(ir, GPD_STATUS).Value ' MsgBox GPD_ID & " " & GPD_ITEM With wkbook.Sheets(GP_Sheet).Range(GP_ID_RANGE) Set Rng_ID = .Find(What:=GPD_ID_VAL, _ After:=.Cells(1), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False) End With With wkbook.Sheets(GP_Sheet).Range(GP_ITEM_RANGE) Set Rng_ITEM = .Find(What:=GPD_ITEM_VAL, _ After:=.Cells(1), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False) End With If Not Rng_ITEM Is Nothing And Rng_ID Is Nothing Then With wkbook.Sheets(GP_Sheet) .Range(Left(GP_ID_RANGE, 1) & get_row(Rng_ITEM.Address)).Value = "'" & GPD_ID_VAL .Range(GP_ModifyDate_COL & get_row(Rng_ITEM.Address)).Value = Format(Now, "mm/dd/yyyy - hh:mm:ss") End With End If LastRow = wkbook.Sheets(GP_Sheet).Cells(Cells.Rows.Count, Left(GP_ITEM_RANGE, 1)).End(xlUp).Row + 1 ' MsgBox "LastRow" & LastRow If Rng_ID Is Nothing Or Rng_ITEM Is Nothing Then ' MsgBox "Create Record" '~ Create Record wkbook.Sheets(GP_Sheet).Cells(LastRow, GP_ID_COL).Value = "'" & GPD_ID_VAL wkbook.Sheets(GP_Sheet).Cells(LastRow, GP_ITEM_COL).Value = GPD_ITEM_VAL wkbook.Sheets(GP_Sheet).Cells(LastRow, GP_MANF_SYS_COL).Value = "PHOENIX" wkbook.Sheets(GP_Sheet).Range(GP_ModifyDate_COL & LastRow).Value = Format(Now, "mm/dd/yyyy - hh:mm:ss") End If '~~ Update Block If Not Rng_ID Is Nothing And Not Rng_ITEM Is Nothing Then If Rng_ID.Areas.Count 1 Or Rng_ITEM.Areas.Count 1 Then ' MsgBox "More then one found" Else If get_row(Rng_ID.Address) = get_row(Rng_ITEM.Address) Then '~~ mark sure that System is PHOENIX If wkbook.Sheets(GP_Sheet).Cells(get_row(Rng_ID.Addre ss), GP_MANF_SYS_COL) = "PHOENIX" Then ' MsgBox "Update Date/Time" ' wkbook.Sheets(1).Range(Rng_ID.Address).Value = GPD_RBO With wkbook.Sheets(GP_Sheet) If GPD_DT_FMT_COMP_VAL 0 Then '~~ FM Setup Date .Range(GP_DT_FMT_COMP_ADD & get_row(Rng_ID.Address)).Value = GPD_DT_FMT_COMP_VAL End If '~~ Status .Range(GP_STATUS_ADD & get_row(Rng_ID.Address)).Value = GPD_STATUS_VAL '~~ Modify Date .Range(GP_ModifyDate_COL & get_row(Rng_ID.Address)).Value = Format(Now, "mm/dd/yyyy - hh:mm:ss") End With End If End If End If End If Next ir End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search, Match, And return corresponding column value | Excel Worksheet Functions | |||
Search a column to match textbox value from a form | Excel Programming | |||
Search Column, If match found then add two values. | Excel Programming | |||
search column, hyperlink, offset, substitute, match (omg) | Excel Discussion (Misc queries) | |||
is there a way to search with vlookup to match more than 1 column | Excel Discussion (Misc queries) |