Find within a selection

[cjjn]

Hi,

How is everyone today?

Hope someone could offer some quick help.
I am trying to use VBA to find and select a cell within a certain column. The criteria is based on what the user selects from a list box.

Here is the code I have so far.
I get an error:
Object variable or with block variable not set.

Columns("D").Select
Selection.Find(What:=UserForm1.lbWeek.Value, LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True).Activate

Thanks in advance.

 

[Loomah]

Hi
That error could be caused by the find criteria not existing on the sheet

Does the value of UserForm1.lbWeek exist on your sheet, in column D?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[cjjn]

Hi Loomah,

yes it does.

they are dates - does that matter?
would it be better if i had the value of the listbox put in a cell and then reference that cell's value in my search instead?

Thanks for your help.

C

 

[xlbo]

dates can be a right little pain with the FIND function. Best bet is to FORMAT the date - it is also preferable to put an error trap in for if the date is not found:

dim fCell as range
Set fCell = Selection.Find(What:=format(UserForm1.lbWeek.Value,selection.cells(1,1).numberformat) LookIn:=xlFormulas, LookAt:=xlWhole, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True

If not fCell is nothing then
'Search successful
else
'Not successful
end if

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question