Target Row - 2 Criteria

dbnfl

Board Regular
Joined
Aug 11, 2019
Messages
57
Hello,

I have the below code running on a form. It works of the "CBEditContainer" (ComboBox). on the same form i have TextBox "TBAgentJobNo" can i add this to below code so when it searches and opens the next form it searchs base on both the ComboBox "CBEditContainer" and TextBox "TBAgentJobNo".

Do I need to make Agent Job No. column Dynamic as well?

Private Sub CommandButton1_Click()

Dim TargetRow As Integer

TargetRow = Application.WorksheetFunction.Match(CBEditContainerNo, Sheets("Data").Range("ContainerNoDyn"), 0)

Sheets("Engine").Range("B5").Value = TargetRow
Unload AddEDO

AddEDODetails.TBNFLJobNo = Sheets("Data").Range("Data_Start").Offset(TargetRow, 0).Value
AddEDODetails.CBJobStatus = Sheets("Data").Range("Data_Start").Offset(TargetRow, 1).Value
AddEDODetails.TBJobType = Sheets("Data").Range("Data_Start").Offset(TargetRow, 2).Value
AddEDODetails.TBAgent = Sheets("Data").Range("Data_Start").Offset(TargetRow, 3).Value
AddEDODetails.TBAgentJobNo = Sheets("Data").Range("Data_Start").Offset(TargetRow, 4).Value
AddEDODetails.TBDeliveryClient = Sheets("Data").Range("Data_Start").Offset(TargetRow, 6).Value
AddEDODetails.TBNoOfItems = Sheets("Data").Range("Data_Start").Offset(TargetRow, 27).Value
AddEDODetails.CBItemType = Sheets("Data").Range("Data_Start").Offset(TargetRow, 28).Value
AddEDODetails.TBOk_to_Book_Date = Sheets("Data").Range("Data_Start").Offset(TargetRow, 31).Value
AddEDODetails.TBVessel = Sheets("Data").Range("Data_Start").Offset(TargetRow, 42).Value
AddEDODetails.TBInVoyage = Sheets("Data").Range("Data_Start").Offset(TargetRow, 44).Value
AddEDODetails.CBShippingLine = Sheets("Data").Range("Data_Start").Offset(TargetRow, 50).Value
AddEDODetails.TBPin = Sheets("Data").Range("Data_Start").Offset(TargetRow, 55).Value
AddEDODetails.TBEDOWeight = Sheets("Data").Range("Data_Start").Offset(TargetRow, 53).Value
AddEDODetails.CBPlaceOfEmptyReturn = Sheets("Data").Range("Data_Start").Offset(TargetRow, 51).Value
AddEDODetails.TBEDOSealNo = Sheets("Data").Range("Data_Start").Offset(TargetRow, 54).Value
AddEDODetails.TBContainerNo = Sheets("Data").Range("Data_Start").Offset(TargetRow, 16).Value
AddEDODetails.CBContainerType = Sheets("Data").Range("Data_Start").Offset(TargetRow, 17).Value
AddEDODetails.TBTSSealNo = Sheets("Data").Range("Data_Start").Offset(TargetRow, 25).Value
AddEDODetails.TBTSWeight = Sheets("Data").Range("Data_Start").Offset(TargetRow, 29).Value

AddEDODetails.Show


End Sub

Regards,

Dale.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,096
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
Dim TargetRow1 As Integer
Dim TargetRow2 As Integer

TargetRow1 = Application.WorksheetFunction.Match(CBEditContainerNo, Sheets("Data").Range("ContainerNoDyn"), 0)
TargetRow2 = Application.WorksheetFunction.Match(TBAgentJobNo, Sheets("Data").Range("ContainerNoDyn"), 0)
If TargetRow1 <> TargetRow2 Then Exit Sub
 

dbnfl

Board Regular
Joined
Aug 11, 2019
Messages
57
Dim TargetRow1 As Integer Dim TargetRow2 As Integer TargetRow1 = Application.WorksheetFunction.Match(CBEditContainerNo, Sheets("Data").Range("ContainerNoDyn"), 0) TargetRow2 = Application.WorksheetFunction.Match(TBAgentJobNo, Sheets("Data").Range("ContainerNoDyn"), 0) If TargetRow1 <> TargetRow2 Then Exit Sub
Hello,

Thank you for the reply. I have tried adding your code. I know i'm missing something:(

VBA Code:
Option Explicit


Private Sub CBEditContainerNo_Change()
Dim i As Long, LastRow As Long
LastRow = Sheets("Data").Range("R" & Rows.Count).End(xlUp).Row


For i = 2 To LastRow
If Sheets("Data").Cells(i, "R").Value = Me.CBEditContainerNo Then
Me.TBAgentJobNo = Sheets("Data").Cells(i, "F").Value
End If
Next


End Sub


Private Sub CommandButton1_Click()


'Dim TargetRow As Integer


'TargetRow = Application.WorksheetFunction.Match(CBEditContainerNo, Sheets("Data").Range("ContainerNoDyn"), 0)


Dim TargetRow1 As Integer
Dim TargetRow2 As Integer


TargetRow1 = Application.WorksheetFunction.Match(CBEditContainerNo, Sheets("Data").Range("ContainerNoDyn"), 0)
TargetRow2 = Application.WorksheetFunction.Match(TBAgentJobNo, Sheets("Data").Range("AgentJobNoDyn"), 0)
If TargetRow1 <> TargetRow2 Then Exit Sub


Sheets("Engine").Range("B5").Value = TargetRow (THIS LINE OF CODE)
Unload AddEDO


AddEDODetails.TBNFLJobNo = Sheets("Data").Range("Data_Start").Offset(TargetRow1, 0).Value
AddEDODetails.CBJobStatus = Sheets("Data").Range("Data_Start").Offset(TargetRow1, 1).Value
AddEDODetails.TBJobType = Sheets("Data").Range("Data_Start").Offset(TargetRow1, 2).Value
AddEDODetails.TBAgent = Sheets("Data").Range("Data_Start").Offset(TargetRow1, 3).Value
AddEDODetails.TBAgentJobNo = Sheets("Data").Range("Data_Start").Offset(TargetRow1, 4).Value
AddEDODetails.TBDeliveryClient = Sheets("Data").Range("Data_Start").Offset(TargetRow1, 6).Value
AddEDODetails.TBNoOfItems = Sheets("Data").Range("Data_Start").Offset(TargetRow1, 27).Value
AddEDODetails.CBItemType = Sheets("Data").Range("Data_Start").Offset(TargetRow1, 28).Value
AddEDODetails.TBOk_to_Book_Date = Sheets("Data").Range("Data_Start").Offset(TargetRow1, 31).Value
AddEDODetails.TBVessel = Sheets("Data").Range("Data_Start").Offset(TargetRow1, 42).Value
AddEDODetails.TBInVoyage = Sheets("Data").Range("Data_Start").Offset(TargetRow1, 44).Value
AddEDODetails.CBShippingLine = Sheets("Data").Range("Data_Start").Offset(TargetRow1, 50).Value
AddEDODetails.TBPin = Sheets("Data").Range("Data_Start").Offset(TargetRow1, 55).Value
AddEDODetails.TBEDOWeight = Sheets("Data").Range("Data_Start").Offset(TargetRow1, 53).Value
AddEDODetails.CBPlaceOfEmptyReturn = Sheets("Data").Range("Data_Start").Offset(TargetRow1, 51).Value
AddEDODetails.TBEDOSealNo = Sheets("Data").Range("Data_Start").Offset(TargetRow1, 54).Value
AddEDODetails.TBContainerNo = Sheets("Data").Range("Data_Start").Offset(TargetRow1, 16).Value
AddEDODetails.CBContainerType = Sheets("Data").Range("Data_Start").Offset(TargetRow1, 17).Value
AddEDODetails.TBTSSealNo = Sheets("Data").Range("Data_Start").Offset(TargetRow1, 25).Value
AddEDODetails.TBTSWeight = Sheets("Data").Range("Data_Start").Offset(TargetRow1, 29).Value


AddEDODetails.Show




End Sub






Private Sub UserForm_Click()


End Sub
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,096
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
If TargetRow1.Row <> TargetRow2.Row Then Exit Sub
 

dbnfl

Board Regular
Joined
Aug 11, 2019
Messages
57
VBA Code:
If TargetRow1.Row <> TargetRow2.Row Then Exit Sub

Hello,

Have adjusted that code line and now get error "Invalid qualifer"

Code:
Option Explicit


Private Sub CBEditContainerNo_Change()
Dim i As Long, LastRow As Long
LastRow = Sheets("Data").Range("R" & Rows.Count).End(xlUp).Row


For i = 2 To LastRow
If Sheets("Data").Cells(i, "R").Value = Me.CBEditContainerNo Then
Me.TBAgentJobNo = Sheets("Data").Cells(i, "F").Value
End If
Next


End Sub


Private Sub CommandButton1_Click()





Dim TargetRow1 As Integer
Dim TargetRow2 As Integer


TargetRow1 = Application.WorksheetFunction.Match(CBEditContainerNo, Sheets("Data").Range("ContainerNoDyn"), 0)
TargetRow2 = Application.WorksheetFunction.Match(TBAgentJobNo, Sheets("Data").Range("AgentJobNoDyn"), 0)
If TargetRow1.Row <> TargetRow2.Row Then Exit Sub


Sheets("Engine").Range("B5").Value = TargetRow1 <> TargetRow2


Unload AddEDO


AddEDODetails.TBNFLJobNo = Sheets("Data").Range("Data_Start").Offset(TargetRow1 <> TargetRow2, 0).Value
AddEDODetails.CBJobStatus = Sheets("Data").Range("Data_Start").Offset(TargetRow1 <> TargetRow2, 1).Value
AddEDODetails.TBJobType = Sheets("Data").Range("Data_Start").Offset(TargetRow1 <> TargetRow2, 2).Value
AddEDODetails.TBAgent = Sheets("Data").Range("Data_Start").Offset(TargetRow1 <> TargetRow2, 3).Value
AddEDODetails.TBAgentJobNo = Sheets("Data").Range("Data_Start").Offset(TargetRow1 <> TargetRow2, 4).Value
AddEDODetails.TBDeliveryClient = Sheets("Data").Range("Data_Start").Offset(TargetRow1 <> TargetRow2, 6).Value
AddEDODetails.TBNoOfItems = Sheets("Data").Range("Data_Start").Offset(TargetRow1 <> TargetRow2, 27).Value
AddEDODetails.CBItemType = Sheets("Data").Range("Data_Start").Offset(TargetRow1 <> TargetRow2, 28).Value
AddEDODetails.TBOk_to_Book_Date = Sheets("Data").Range("Data_Start").Offset(TargetRow1 <> TargetRow2, 31).Value
AddEDODetails.TBVessel = Sheets("Data").Range("Data_Start").Offset(TargetRow1 <> TargetRow2, 42).Value
AddEDODetails.TBInVoyage = Sheets("Data").Range("Data_Start").Offset(TargetRow1 <> TargetRow2, 44).Value
AddEDODetails.CBShippingLine = Sheets("Data").Range("Data_Start").Offset(TargetRow1 <> TargetRow2, 50).Value
AddEDODetails.TBPin = Sheets("Data").Range("Data_Start").Offset(TargetRow1 <> TargetRow2, 55).Value
AddEDODetails.TBEDOWeight = Sheets("Data").Range("Data_Start").Offset(TargetRow1 <> TargetRow2, 53).Value
AddEDODetails.CBPlaceOfEmptyReturn = Sheets("Data").Range("Data_Start").Offset(TargetRow1 <> TargetRow2, 51).Value
AddEDODetails.TBEDOSealNo = Sheets("Data").Range("Data_Start").Offset(TargetRow1 <> TargetRow2, 54).Value
AddEDODetails.TBContainerNo = Sheets("Data").Range("Data_Start").Offset(TargetRow1 <> TargetRow2, 16).Value
AddEDODetails.CBContainerType = Sheets("Data").Range("Data_Start").Offset(TargetRow1 <> TargetRow2, 17).Value
AddEDODetails.TBTSSealNo = Sheets("Data").Range("Data_Start").Offset(TargetRow1 <> TargetRow2, 25).Value
AddEDODetails.TBTSWeight = Sheets("Data").Range("Data_Start").Offset(TargetRow1 <> TargetRow2, 29).Value


AddEDODetails.Show




End Sub






Private Sub UserForm_Click()


End Sub
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,096
Office Version
  1. 2016
Platform
  1. Windows
Sorry, my mistake.

Sheets("Engine").Range("B5").Value = TargetRow1
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,614
Messages
5,838,382
Members
430,543
Latest member
Elaine E

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top