PDA

View Full Version : Solved: Range.Find returning Nothing



lynnnow
02-14-2013, 10:24 PM
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. :help

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
02-14-2013, 11:47 PM
did you try findnext method?

lynnnow
02-14-2013, 11:50 PM
I can't use .FindNext since the date to be sought may not be the same.

mohanvijay
02-14-2013, 11:54 PM
can you post workbook in correct format. i can't download your workbook it says file is in incorrect format

lynnnow
02-14-2013, 11:59 PM
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
02-15-2013, 12:16 AM
which macro do you have this problem?
is that macro named "testing"?

lynnnow
02-15-2013, 12:20 AM
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
02-15-2013, 01:36 AM
have you defined range in the name "XCFID"?

lynnnow
02-15-2013, 01:57 AM
Yes,

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

mohanvijay
02-15-2013, 02:06 AM
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
02-15-2013, 03:24 AM
"TotalRange" is used to populate the Spreadsheet object... I'll try putting in the XCFID name in a while...

lynnnow
02-15-2013, 06:18 AM
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
02-17-2013, 11:29 PM
Got the Range.Find to work. Just added this:

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


and it did the trick.