Solved: 2 search criteria

[blackie42]

I have a sheet that has 49 funds in column A & working day dates in col B so the sheet is around 250 * 49 rows big. So row 1 is Col A CMF F, Col B is 29/11/2007, 2 is CMF F and 30/11/2007 etc - 250 times back to last year then the next fund is listed. (There also other figures in cols c, d & e)

Some of the dates are duplicated in some of the funds (but not all and i know which ones) - I want to find these with macro (as its a weekly job)and doing some other stuff e.g. amalgamating other info in the row (which I can do)

I set up 2 searches 1 for finding the fund e.g. CMF F & a second that finds the date 3/12/2007. Problem is although it finds the fund when it runs the second bit it goes back top of column and starts again.

So to find the entry that duplicated on 2 rows CMF F . 03/12/2007

egg = "CMF F"
nog = "03/12/2007"

set c = range("A:A").find(What:=egg)

set c1 = range("B:B").find(What:=nog)

I want the active cell to be set in col A on the 1st of the 2 rows that show as CMF F & 03/12/2007. I know I'm missing some bits here and would appreciate any help. Is there a better/easier way?

thanks

[Bob Phillips]

egg = "CMF F"
nog = DateSerial(2007, 12, 3)

Cells(ActiveSheet.Evaluate("MATCH(1,(A1:A12500=""" & egg & """)*(B1:B12500=" & CLng(nog) & "),0)"), "A").Select

[blackie42]

Hi,

I copied and pasted code in to module but can't seem to get this to work - probably missed a crucial bit. When I step thru I get 'runtime error 13' 'Type mismatch'

[blackie42]

I'm wanting to find the 2nd 3/12/2007 line (that I highlighted) and delete the row.

thanks

[Bob Phillips]

You have a trailing space in your data, so it is not 'CMF F' but 'CMF F '

[Bob Phillips]

I guess that you could use this



Sub finddate()

Dim egg As String
Dim nog As Date

egg = "CMF F"
nog = DateSerial(2007, 12, 3)

With ActiveSheet
Cells(.Evaluate("MATCH(1,(TRIM(A1:A12500)=""" & egg & """)*(B1:B12500=" & CLng(nog) & "),0)"), "A").Select
End With

End Sub


Note that egg only has the single embedded space for testing.

[blackie42]

Thanks for your help - I replaced egg with "CMF F " and it works.

Definitely a keeper - just wish I could come up with something like that - not even sure how it works.

regards

Jon

[Zack Barresse]

It works on the pretense that the Evaluate() method will decipher an array formula ..

'Whole
"MATCH(1,(TRIM(A1:A12500)=""" & egg & """)*(B1:B12500=" & CLng(nog) & "),0)")

'First expression
TRIM(A1:A12500)=""" & egg & """)

'Second expression
B1:B12500=" & CLng(nog)

The two expressions are compared together via multiplication, thus returning a single array, from two, where both conditions are true. This is placed in the MATCH function..
MATCH(1, ReturnedArray, 0)
The 0 end syntax grabs an exact match, thus an error is returned if no match is found. The 1 first syntax says it will grab a 1, which means it will find the first 1 value it comes to going down the range. Thus the first match of both criteria found will be returned, as a record number (generally thought of a the row returned), of the range specified. Couple that with the Cells method...
Cells(ReturnedRowFoundHere, "A").Select
That will grab the row/record from the Evaluate method and Select that cell, as requested.

Hope this explanation helps and doesn't hinder.. :)

[blackie42]

and thanks for the explanation....

regards

Jon