Code for multiple search

Hello, I want to write a code that allows me to look in multiple columns for specified entries.
For example, I want to search column A for the word "John", if found, I want to check column B for hours 1:00, if found I want to check column C for hours 1:00, finally if found, I want to check column D for the letter "C".
And in the end, return the number of the row so I can use it in my code.
In the sample below, the code should return the number 9.
I have a lot of entries in a sheet1, I am trying to build a code, that will look in the sheet1 if specific entries (which I provide in sheet2) exist.
Is this possible?
Any help will be appreciated. Thanks

Hi

Here is a sumproduct formula that will find the row (assuming that there is only 1 unique result to be found)

=SUMPRODUCT(--(A2:A9="John"),--(B2:B9=TIMEVALUE("01:00")),--(C2:C9=TIMEVALUE("01:00")),--(D2:D9="C"),ROW(D2:D9))

You could either use this directly on the sheet to get your result, or use the EVALUATE function to have it work directly within your script.

rylo

Thanks rylo, how do I write it in vba code?

HI

one way

Please Login or Register  to view this content.

rylo

Thanks for your help, and sorry for the late reply.
I tried using the code you wrote, but I think there is something wrong with what I am writing, because I am getting an error.
this is my code:

Please Login or Register  to view this content.

By the way, this code is not related to the sample I posted.

Hi

Only looked at the one line but try something like

Please Login or Register  to view this content.

rylo

It is not working. I still get
run time error 13
type mismatch

ok here is a sample.
You see I am making a logbook, where I enter flight details in the AllData sheet, and when I select "ok" in column L, the code should automatically copy the information to the pilots sheets and make some adjustments.
The code I asked before should work with this one when I select "CLR".
For example, if I select CLR in AllData sheet in row 6, the code should go to the sheet (pilot3) and delete the row that has the same information as this one. (in this case "row 5" in "pilot3" should be deleted).
And also, it should do the same for the co-pilot (in this case "row 5" in sheet "pilot1" should be deleted).

Hi

It is related to the way you have your if / end if blocks positioned.

What I did was actually indent your code logically and from this I found that you have

Please Login or Register  to view this content.

but you don't have an else for that test. All the following if / end if and with statements are all related to that block, so it is not doing any testing for the 'CLR' option when you do the selection.

I've put in an else statement that will cover of the selection of the 'CLR' option to just show a messagebox of the target.value

Please Login or Register  to view this content.

See if that gets you going.

rylo

Hi

Another way to do this would be to just delete the row in alldata for a 'CLR', then have worksheet activation events for each of the sheets so that they are updated with relevant data when they are activated.

This is for the pilot1 sheet, missing the logic for the PIC/SIC.

Please Login or Register  to view this content.

rylo

Hi

Just because you have put in the with statement to action myDestSheet3, the formula being evaluated won't refer to that sheet. So the evaluate / sumproduct approach will have to include the relevant sheet name

Please Login or Register  to view this content.

The above does at least not error, but on the quick test I did, it didn't bring back a meaningful row. I'll leave that to you to investigate.

rylo

Yes it does no error, but its value is always 0.
I don't know how to continue...thanks for your help anyway