Error finding match looking for intersection

[Guest]

Dear Rescue Team,

This code has been working perfectly well :

Set rngColumnToSearch = ActiveSheet.Range("C2:C500")
Set rngRowToSearch = ActiveSheet.Range("a2:IV2")
Set rngColumnFound = rngColumnToSearch.Find(What:=name, LookIn:=xlValues)
Set rngRowFound = rngRowToSearch.Find(What:=dt, LookIn:=xlValues)

finding name in rngColumnToSearch and
dt in rngRowToSearch.

I have been re-writing some code (but not this) and now this section won't
find a matching dt when the date is staring me in the face!

The tooltip in debug mode says that rngRowFound is Nothing!

As I said, I have columns of earlier data to prove it used to work. Dates
appear to be in same format... what could be the problem??

Thanks in anxious anticipation,

Matilda

 

[Die_Another_Day]

Are the ActiveWorkbook and ActiveSheet correct? Sometimes it's better
to statically assign ranges like this:
Set rngColumnToSearch =
Workbooks("YourWorkbook.xls").Sheets("YourSheet").Range("C2:C500")

Charles

 

[Guest]

Thanks for this, Charles.
Yes, the line before the extract I posted activates the workbook.
Strangely, if I add a debug line:
application.match(clong(dt), C2:C500)
it finds the correct date without any problem!

That had me thinking it must be a date format thing, but the same variable
(dt) populates the search cell in the first place, then is used again as
search criteria in this bit. How can there be a format change in there?

By the way, previous line finds the name without problem.

I know how tough your job must be because I am probably missing something
obvious and misleading you...
but any help appreciated!


Matilda

 

[Guest]

Actually, I have decided to try another tack, but lack the syntax!
Since I can find a match for both the name in the name column, and the date
in the date row, how can I write the reference to the intersection using the
variables?

I find range referencing horribly confusing, especially when using variables
for cell numbers, and all my attempts are treated with contempt by excel
compiler :-(

For example: Range.columns(dtCnt).rows(nameCnt) doesn't work.

Sorry to be such a pest in matters so basic!

Many thanks,

Matilda

 

[Die_Another_Day]

Try Cells(nameCnt,dtCnt)

Charles

Actually, I have decided to try another tack, but lack the syntax!
Since I can find a match for both the name in the name column, and the date
in the date row, how can I write the reference to the intersection using the
variables?

I find range referencing horribly confusing, especially when using variables
for cell numbers, and all my attempts are treated with contempt by excel
compiler :-(

For example: Range.columns(dtCnt).rows(nameCnt) doesn't work.

Sorry to be such a pest in matters so basic!

Many thanks,

Matilda