How to Search two column value are match

[moonhk]

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.Address),
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