Error finding match looking for intersection
 

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

Error finding match looking for intersection
 

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


Matilda wrote:
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

Error finding match looking for intersection
 

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

"Die_Another_Day" wrote:

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


Matilda wrote:
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

Error finding match looking for intersection
 

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" wrote:

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


Matilda wrote:
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

Error finding match looking for intersection
 

Try Cells(nameCnt,dtCnt)

Charles

Matilda wrote:
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" wrote:

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


Matilda wrote:
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