Solved: Range.Find returning Nothing

[lynnnow]

I'm completely stumped why the Range.Find will find the data on the first run and then fail subsequently, needing me to close Excel and restart the application. I get a 1004 error. I've attached the workbook for same.

The macro is initiated with Ctrl+L. There are two userforms in the file, but I'm working on UserForm2. UserForm1 is my old code that works fine. I needed to upgrade and hence the effort.

I have used a spreadsheet control that uses data from another sheet to only display the data on the form. The .Find function has to look for a date (from Textbox1) and return the column number and the ActiveCell's row is used in an Intersect function to get the coordinates for the first cell, then I use the .Find function to get the last cell in a similar way and get those coordinates. This works superbly for the first run of the macro. I've made the userform reload after the entry is saved until I close the userform.

I'm at a loss to understand why the .Find function doesn't return a range on the second run.

Any hints and suggestions on what I could improve are also appreciated.

I'm using Win XP and Office 2003 to test at work and Win 7 and Office 2010 at home to test.

[mohanvijay]

did you try findnext method?

[lynnnow]

I can't use .FindNext since the date to be sought may not be the same.

[mohanvijay]

can you post workbook in correct format. i can't download your workbook it says file is in incorrect format

[lynnnow]

Please try again, there was a missing range name in the previous version. I was probably updating the file when you tried downloading it.

[mohanvijay]

which macro do you have this problem?
is that macro named "testing"?

[lynnnow]

Sub FamCollChecker is the macro that is triggered with Ctrl+L.

The macro bugs out at the Textbox1_Exit of the UserForm2, at the second run.

The Testing macro is of no consequence... I was just trying stuff.

[mohanvijay]

have you defined range in the name "XCFID"?

[lynnnow]

Yes,

XCFID refers to =OFFSET('Family Collection Checker'!$A$3,0,1,COUNTA('Family Collection Checker'!$A$3:$A$500),1)

[mohanvijay]

in attached workbook there is no defined name as "XCFID" there is name defined as "TotalRange" i changed the "TotalRange" to "XCFID" in "Userform2" and ran works perfectly

Check with names

[lynnnow]

"TotalRange" is used to populate the Spreadsheet object... I'll try putting in the XCFID name in a while...

[lynnnow]

I changed a bit of tact and this is what I've come up with, however, I'm still running into the "Nothing" error on second run...

[lynnnow]

Got the Range.Find to work. Just added this:

[VBA] Set rngCol1 = Range("XCFMonths").Find(CDate(TextBox1.Value), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows)
[/VBA]

and it did the trick.