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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
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,091
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,091
Office Version
  1. 2016
Platform
  1. Windows
Sorry, my mistake.

Sheets("Engine").Range("B5").Value = TargetRow1
 

Watch MrExcel Video

Forum statistics

Threads
1,129,660
Messages
5,637,626
Members
416,977
Latest member
kdoederlein

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