Trying to updated sheet based of location returned by Vlookup

Hi,

Can you fix your code as per rule #3

Thanks.

http://www.excelforum.com/forum-rule...rum-rules.html

Thanks for pointing that out Fred... updated it.

The best way to achieve what you want is with a search function in VBA rather than lookups.

Can you attached a dummy file and I will help you build it.

Thanks

Here is a short dummy file I put together to show what i'm trying to do. Just need to approve or disapprove the address. I might even switch to a radiobutton but for now need to figure out how to update it.

Thank you for your help.

Mike

Originally Posted by fredlo2010

The best way to achieve what you want is with a search function in VBA rather than lookups.

Can you attached a dummy file and I will help you build it.

Thanks

Mike,

Look at the file I have attached for you. It does what you are looking for.

This is the code to under the form1 (with a regular textbox)

Please Login or Register  to view this content.

And this is the code under form 2. This one is more efficient and least prone to errors.

Please Login or Register  to view this content.

With this code you don't even need a Button to submit your choice. But you can learn about that latter (hint you can click th radiobutons)

Also notice how I have given meaningful names to the objects (except the labels because we are not manipulating them) its easier to associate txtAddress with an address that it is textbox2

FormsTest1.xlsm

Thanks

This should do.

Please Login or Register  to view this content.

Fred,

This worked great!

Can't thank you enough!! I definietly like your approach better than mine.

Thanks so much!!

Mike

Hi Jindon,

I was able to resolve it with Freds help, both of your approaches look very similar.

Thank you for assisting, this form saved me so much time!

Mike

Originally Posted by mds219

Hi Jindon,

I was able to resolve it with Freds help, both of your approaches look very similar.

Thank you for assisting, this form saved me so much time!

Mike

No, completely different approach.

Hi Fred,

Can you tell me the releveance of the ".Address" at the end of the code snippet below? I changed the header name and is still works so I supsect it might have something to do with formatting.

Please Login or Register  to view this content.

Thank you,

Mike

the ending ".Address" will return the address (always absolute) of the range.

Please Login or Register  to view this content.

So its just the way i chose to name the variable " GetAddress" and you "GetElement"

btw if you changed it there you will also have to modify it in the other procedures.

Thanks

Thank you Fred, I was able to get it working now. I changed it around a bit, I made the radiobuttons real time and took away the submit button - it was real easy after the example you did for me.

My final endeavor is to put a scroll bar in place to scroll through the records - which I'm working on at the moment. any advice for the approach? I was thinking of taking the address (of the current cell) and just adding or subtracting for each click

Again, cannot thank you enough!

Mike

It would be better for this case to instead of using the address "A23" you use the cell denomination of it: Cells(23,1) this will make that addition easier. Cells(row number, column number)


Thanks

Thanks Fred,

How does this look? I am planning to put the following code in my ScrollBar1_keyUp

Please Login or Register  to view this content.

And of course (-1,0) for the keyDown click.

Thanks so much!!!