Find Method

**ilovedurango** · 07-19-2007, 04:12 PM

This is a challenge!

I need help with the 'find' method. In one sheet, I enter an X in a cell and it triggers a user form. This user form contains many fields that the user may or may not fill with data. This data then needs to be entered on a different sheet. I need to be able to find the correct row to enter this data with (and the next available row will not do, it needs to be a row containing the job the data pertains to).

I'm trying to use the find method to find the job on the other sheet, but I can't select the cell (or range or whatever) once I've found it. I need to activate it and then paste data relative to that cell (or just paste data in cells relative to it - same row, different column).

This is the code I based my find method off of. I am open to better suggestions.

Thank you!

Please Login or Register  to view this content.

**boylejob** · 07-19-2007, 07:21 PM

ilovedurango,

See if this helps. It is a bit more complicated, but it should help you get the row that your search item resides on.

Please Login or Register  to view this content.

**ilovedurango** · 07-20-2007, 08:36 AM

Sadly, that didn't work.

When I run the macro, it highlights ".Row" in the code below.

Please Login or Register  to view this content.

and gives me the error message:
Compile Error:
Method or Data member not found

I'm running excel 2000....is there a way to enter ".Row" that excel 2000 understands?

Thank you

**boylejob** · 07-20-2007, 09:09 AM

ilovedurango,

I put the other code out there in a hurry and did not test it. I should not have done that because there were a couple of error. I certainly apologize.

Try this version and see if it works on your end. I tested it on my end and it came up with the correct answer.

Please Login or Register  to view this content.

**ilovedurango** · 07-20-2007, 09:21 AM

Hola!

Thank you, that code does find the value I want, so now I need to select the cell that it found the data in - this is where I run into my problem...I don't know how to code that!

Muchas muchas muchas gracias in advance!

**boylejob** · 07-20-2007, 09:29 AM

If you change.

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

This will put the address of the found cell in lRow and select that cell.

Since I am not exactly sure what you are trying to accomplish, I am giving you very generalized code. Hopefully, you can modify what I am giving you to meet your needs.

Hope this helps!

**ilovedurango** · 07-20-2007, 09:45 AM

I will explain, but I can upload a dummy file as well if you need to really see what is going on.

I have many automatic processes in my workbook. On sheet one, i have a worksheet_Change event that triggers Instance1: data transfer from sheet1 to sheet2 and another trigger for Instance2: sheet1 to sheet3. When Instance2 occurs, there there is a userform that pops up, and various fields may or may not be entered. when the user presses 'OK', this data needs to be entered on sheet 2, in the same row as previously entered data. That is where the find method comes in.

I need to have a cell selected on sheet 2 so that I can enter the data directly or reference the selected cell and enter the data from the user form when the user presses OK.

It's a lot of steps and confusing.....but does that make sense?

and on top of all that...the change in the lines of code selects a different cell on sheet one, not on the other sheet i was searching in. If you can't tell, I'm still new at this :-)

**boylejob** · 07-20-2007, 12:01 PM

ilovedurango,

Lets see if I can explain a few things and maybe it will head you in the right direction.

In the code that I sent you I set the actual worksheet equal to a variable.

Please Login or Register  to view this content.

which makes your code simpler and keeps you from having to select things. Selecting things ultimately slows down your code.

Many of my processes use data coming from multiple workbooks. I set each workbook and worksheet to be a varibable. I might also be jumping around from worksheet to worksheet in the same workbook. Same principle applies. Here are some examples.

This one is for worksheets within the same workbook.

Please Login or Register  to view this content.

This one is for worksheets in different workbooks

Please Login or Register  to view this content.

Using this idea you can now refer to a cell using the variable

Please Login or Register  to view this content.

and I do not have to select the cell to assign the new value.

Lets look at a modified version of the code I sent to you.

Please Login or Register  to view this content.

I hope this is clear and that it helps you understand things a little better. Mess around with this information and see where it gets you. If you have more questions, ask! I wish I knew about this forum years ago when I was learning the ins and outs of VBA.

Just out of curiosity, where in the world are you located.

**ilovedurango** · 07-20-2007, 02:37 PM

Thank you!

I've been learning how to Dimension things....I just stumble along the way and then if you can't go from a to b, the whole train gets wrecked and it doesn't matter if it's halfway right, the whole dang macro won't run.

That helps a bunch. I would like your advice though....

On my UserForm code, should I code cmdOK with a bunch of variants/strings and then to call the macro Find_Value like so....??

Please Login or Register  to view this content.

I've called other macros like this before, but I can only make it work with one argument.

The internet fascinates me about who it connects. I've played chess against a 4th grader in england! I am in Cincinnati, OH. But I'm from San Diego, CA. And I go to school in Phoenix, AZ. I'm going to be a junior in mechanical engineering this coming fall. :-) Your turn!

**boylejob** · 07-20-2007, 04:40 PM

ilovedurango

What exactly are you doing with this code?

Please Login or Register  to view this content.

I normally use a Call statement on my UserForms

Please Login or Register  to view this content.

and then have the sub Find_Value residing in a standard Module. In the Find_Value sub I would gather my data off of the UserForm and then unload it. Something like

Please Login or Register  to view this content.

Not sure if this is what you are doing or not. If you want me to look closer you might want to consider posting your workbook or a sample of it. That would allow me to see exactly what you are doing. It would also let others see. I am definitely not an expert nor do I program for a living, but some on this forum are and do.

For the side note, I am the Director of Financial Aid at a small community college in NC. I have been programming in various languages since 1988 to make my job easier (if you can do that) and have been working with VBA for about 7 years. I constantly change the way I code based on new things I learn and this forum has taught me all kinds of things that have helped to stream line my programs.

**ilovedurango** · 07-23-2007, 10:44 AM

I am uploading my file, as I need a little help.

This workbook has dozens of macros....I am concerned with the code for the user form 'frmAwardedTo'

On the "bid list" sheet, when you enter an "X" into the Quoted? column, the job data transfers to the "Quote Log"

When you enter an X in the cell adjacent, the user form "Awarded To" pops up.

I can't get the data I've entered in "Awarded to" to plug itself into the "Quote Log" sheet. I used the suggested "find value" code to find the job in the Quote log, but I can't get it to work once I"ve plugged in all my data. actually, it really doesn't do anything!

The code to unload the user form is in module 3

Thank you for all your help!

Katie

**boylejob** · 07-23-2007, 11:38 AM

Katie,

I made a few adjustments to you EnterData sub in Module3. Try this and see if it works now.

Please Login or Register  to view this content.

**ilovedurango** · 07-23-2007, 11:47 AM

Thank you Jeff! That works!

Ready for another one?

If the user enters data, and the job was never transferred to the quote log, the message box pops up correctly, but then my automatic macros stop working.

Do you know why this is?

**boylejob** · 07-23-2007, 12:52 PM

Katie,

In your code you use Application.EnableEvents a great deal. I would imagine that somewhere in the error process you change the value to equal False, but it is not turned back to True. Therefore, the events have been disabled and do not fire.

As I have time, I will continue to look through you code to see if I can spot it. Your are more familiar with the code so you may spot it before I do.

That may not even be the problem, but I suspect it is!

**boylejob** · 07-23-2007, 01:23 PM

Katie,

The Application.EnableEvents was the problem. When you encounter the error the sub exits after the message box and does not go back to where it was called from so Application.EnableEvents does not get set back to a value of True

To get the value set back to True I had to create a little sub and I ran it from the Debug Tool Menu.

Please Login or Register  to view this content.

In your Sub EnterData() in Module3 change your Else statement to this

Please Login or Register  to view this content.

See if that resolves your problem.

**ilovedurango** · 07-23-2007, 01:48 PM

That was it!

Thank you so much for all your help. As you can see, this workbook has been quite the masterpiece in the making. I really appreciate all your efforts.

Thank you!