Searching values in a database

[odd_socks17]

Hi all,
I'm writing a code which will (hopefully!) update the value of a cell which is chosen based on Userform Inputs.

I am looking for a way to find a row (and assign it to a variable which will be used to find a cell in that row) based on two variables from my Userform.
Ive tried the IF loop and MATCH methods and havent been able to get them to work (im pretty new to coding).

Sample code:

Quantity at Shop 1	Quantity at Shop 2	Food Group	Name
3	2	Fruit	Apple
5	1	Fruit	Banana
0	3	Vegetable	Celery
6	4	Vegetable	Carrot

I am hoping for a way to find, for example, Row 2, if the user inputs Fruit into a combo box on the form, and Banana in another combo box.
At the moment these combo boxes are being used to define the variable varGroup and varName.

Id like the row value to be assigned to a variable that i can use in the code i am currently using to update the quantity value, shown below:

Please Login or Register  to view this content.

the variable varROWVALUE shown in bold above is what i am looking to define.

Looking for a purely VBA solution. I am using version 15 of Excel (i think!)

Any help is greatly appreciated (and needed), hope your expertise can end the frustrated hours of fruitless experiments.

Many thanks,
odd_socks


Crossposted at:
http://www.vbaexpress.com/forum/show...+in+a+database
http://www.ozgrid.com/forum/showthre...d=1#post794161
https://www.mrexcel.com/forum/excel-...ml#post4849280

[6StringJazzer]

First, you have two rows that match the two values you gave in your example. Do you just want to update the first one you find?

This solution assumes your data starts in column A. It assumes that there must be a value in column C; the first blank indicates the end of the data.

Please Login or Register  to view this content.

I have not tested this but would be glad to do so if you provide your file. Under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

[YasserKhalil]

Cross-Post at this link
http://www.vbaexpress.com/forum/show...-in-a-database

[alansidman]

crossposted: http://www.ozgrid.com/forum/showthre...d=1#post794161

https://www.mrexcel.com/forum/excel-...ml#post4849280

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[odd_socks17]

First, you have two rows that match the two values you gave in your example. Do you just want to update the first one you find?

It looks like ive messed up the formatting of the table (again!), in my database there are more than one incidence of each 'food group' and though the example doesnt show it, there is also duplicates of each name. There should only be one row where BOTH of these values are true, for example, there should be only one row which has both Fruit and Banana and im looking for a way to identify it and use the row value in a variable.

Sorry for any confusion, i can see now the example doesnt reflect the need in my actual database and hope ive cleared everything up.

If possible, can you please provide an explanantion of how your sample code works (pretty new to excel and trying to learn as much as i can, from the best experts i can) if it still applies in light of my explanation.

Many thanks, odd_socks

[odd_socks17]

Crosspost links added, thank you! Sorry for any upset

Apologies,
odd_socks17

[6StringJazzer]

As a new user, please take the time to review our rules. There aren't many, and they are all important.

The code still applies. Here is a little annotation. I don't know how much detail you need. If this is not enough, feel free to ask specific questions.

In the code you posted above I don't know what varColumn refers to so I didn't try to incorporate your code.

Please Login or Register  to view this content.