Target Row - 2 Criteria

D

dbnfl

Board Regular
Joined
Aug 11, 2019
Messages
59
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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
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
 
Upvote 0
mart37 said:
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
Click to expand...
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
 
Upvote 0
VBA Code: 
If TargetRow1.Row <> TargetRow2.Row Then Exit Sub
 
Upvote 0
mart37 said:
VBA Code: 
If TargetRow1.Row <> TargetRow2.Row Then Exit Sub
Click to expand...

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
 
Upvote 0
Sorry, my mistake.

Sheets("Engine").Range("B5").Value = TargetRow1
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Copy visible rows with 2 criteria from one sheet and paste in another on first empty row
Replies
1
Views
574
PhilRJ
P
S
  • Question
Using autofilters to delete rows with 2 criteria.
2
Replies
15
Views
1K
Marcelo Branco
M

Forum statistics

Threads
1,214,424
Messages
6,119,401
Members
448,893
Latest member
AtariBaby

Share this page

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
Back
Top