odd_socks17
06-18-2017, 04:57 AM
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:
QtyPrevious = Cells(varROWVALUE, varColumn).Value
QtyNew = (QtyPrevious + InputQty)
Cells(varROWVALUE, varColumn).Value = QtyNew
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 :)
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:
QtyPrevious = Cells(varROWVALUE, varColumn).Value
QtyNew = (QtyPrevious + InputQty)
Cells(varROWVALUE, varColumn).Value = QtyNew
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 :)