Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
The below code works and return one single name match ( each run) and it basically copies the data from sheet one to sheet two using Name as a key. Now would
like create loop to go through both sheets and compare the names, and
if the name exist in sheet one then copy the corresponding value into sheet two.
<o></o>
Please let me know the possibilities.
Thanks.
<o></o>
Sheet one: “Cargo”
Cargo Summary
Request <o></o>
Total 1 Total 2
Lori Trump
Susan doo
Dii Kon 888 111
Moo Kevin
Ajax James
Alex Trapek
Kevin O'neil
Ming Kii
Kung Kwan
Dii Kii
Nung No
Li Morgan
Total <o></o>
Sheet2:” ORDER”
New Cargo <o></o>
Cargo Captain Sea Name Total A Total B
General ZW N/A Lori Trump 122 66
General DE N/A Moris Bee 40 56
General DD N/A Dii Kon 888 78
General DD N/A Moo Kevin 127 99
General DD N/A Ajax James 24 33
General DD N/A Alex Trapek 231 12
General MM N/A Kevin O'neil 0 10
General Total
Reeefer HU N/A Dii Kon 0 33
Reefer HU N/A Kevin O'neil 515 55
Reefer Total 515
Fish cargo NI N/A Moris Bee 0 67
Fish cargo NI N/A Ajax James 0 83
Fish cargo Total <o></o>
<o> </o>
<o> </o>
‘Here the is the working code:
Option Explicit <o></o>
Option Compare Text <o></o>
Public Captain As String
Public LastRow As Double
Public Variable(15) As Variant
Public VarTemp(15) As Variant <o></o>
Sub Cargo_Data()
Dim iCol As Long
Dim x As Long, x1 As Long, x2 As Long
Dim NewBook As Workbook
Dim bFind As Boolean
Dim Name As String
Dim iTmp As Long <o></o>
Worksheets("Cargo").Select <o></o>
'clear the array variable
For x1 = 1 To 15
Variable(x1) = 0
VarTemp(x1) = 0
Next x1 <o></o>
Captain = Name
If Captain <> " " Then
' GoTo Name
End If <o></o>
ActiveWorkbook.Sheets("Order").Select
' Worksheets("Order").Select
Range("A6").Select
Selection.End(xlDown).Select
LastRow = ActiveCell.Row <o></o>
For x1 = 6 To LastRow
If Cells(x1, 4) = "Dii Kon" Then <o></o>
Call AddTo(Variable(2), Cells(x1, 5))
Call AddTo(Variable(3), Cells(x1, 6)) <o></o>
End If <o></o>
Next x1 <o></o>
'------------------------------------------------------------------------------------------------------
'Imports to the data <o></o>
Worksheets("Cargo").Select
Range("A7").Select
Selection.End(xlDown).Select
LastRow = ActiveCell.Row
For x1 = 7 To LastRow
If Cells(x1, 1) = "Dii Kon" Then <o></o>
For iCol = 2 To 15
Select Case iCol
Case 2 To 3 <o></o>
Cells(x1, iCol) = Variable(iCol)
End Select
Next iCol <o></o>
Exit For
End If
Next x1 <o></o>
End Sub <o></o>
Sub AddTo(ByRef vValue As Variant, vNew As Variant)
If IsNumeric(vNew) Then
If IsNumeric(vValue) Then
vValue = vValue + vNew
ElseIf vValue = "na" Or vValue = "" Then
vValue = vNew
End If
ElseIf vNew = "na" Then
If vValue = 0 Then
vValue = "na"
End If
End If <o></o>
End Sub <o></o>
fficeffice" /><o></o>The below code works and return one single name match ( each run) and it basically copies the data from sheet one to sheet two using Name as a key. Now would
like create loop to go through both sheets and compare the names, and
if the name exist in sheet one then copy the corresponding value into sheet two.
<o
></o>Please let me know the possibilities.
Thanks.
<o
></o> Sheet one: “Cargo”
Cargo Summary
Request <o
></o>Total 1 Total 2
Lori Trump
Susan doo
Dii Kon 888 111
Moo Kevin
Ajax James
Alex Trapek
Kevin O'neil
Ming Kii
Kung Kwan
Dii Kii
Nung No
Li Morgan
Total <o
></o>Sheet2:” ORDER”
New Cargo <o
></o>Cargo Captain Sea Name Total A Total B
General ZW N/A Lori Trump 122 66
General DE N/A Moris Bee 40 56
General DD N/A Dii Kon 888 78
General DD N/A Moo Kevin 127 99
General DD N/A Ajax James 24 33
General DD N/A Alex Trapek 231 12
General MM N/A Kevin O'neil 0 10
General Total
Reeefer HU N/A Dii Kon 0 33
Reefer HU N/A Kevin O'neil 515 55
Reefer Total 515
Fish cargo NI N/A Moris Bee 0 67
Fish cargo NI N/A Ajax James 0 83
Fish cargo Total <o
></o><o
> </o><o
> </o>‘Here the is the working code:
Option Explicit <o
></o>Option Compare Text <o
></o>Public Captain As String
Public LastRow As Double
Public Variable(15) As Variant
Public VarTemp(15) As Variant <o
></o>Sub Cargo_Data()
Dim iCol As Long
Dim x As Long, x1 As Long, x2 As Long
Dim NewBook As Workbook
Dim bFind As Boolean
Dim Name As String
Dim iTmp As Long <o
></o>Worksheets("Cargo").Select <o
></o>'clear the array variable
For x1 = 1 To 15
Variable(x1) = 0
VarTemp(x1) = 0
Next x1 <o
></o>Captain = Name
If Captain <> " " Then
' GoTo Name
End If <o
></o>ActiveWorkbook.Sheets("Order").Select
' Worksheets("Order").Select
Range("A6").Select
Selection.End(xlDown).Select
LastRow = ActiveCell.Row <o
></o>For x1 = 6 To LastRow
If Cells(x1, 4) = "Dii Kon" Then <o
></o>Call AddTo(Variable(2), Cells(x1, 5))
Call AddTo(Variable(3), Cells(x1, 6)) <o
></o>End If <o
></o>Next x1 <o
></o>'------------------------------------------------------------------------------------------------------
'Imports to the data <o
></o>Worksheets("Cargo").Select
Range("A7").Select
Selection.End(xlDown).Select
LastRow = ActiveCell.Row
For x1 = 7 To LastRow
If Cells(x1, 1) = "Dii Kon" Then <o
></o>For iCol = 2 To 15
Select Case iCol
Case 2 To 3 <o
></o>Cells(x1, iCol) = Variable(iCol)
End Select
Next iCol <o
></o>Exit For
End If
Next x1 <o
></o>End Sub <o
></o>Sub AddTo(ByRef vValue As Variant, vNew As Variant)
If IsNumeric(vNew) Then
If IsNumeric(vValue) Then
vValue = vValue + vNew
ElseIf vValue = "na" Or vValue = "" Then
vValue = vNew
End If
ElseIf vNew = "na" Then
If vValue = 0 Then
vValue = "na"
End If
End If <o
></o>End Sub <o
></o>
