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:


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 :)

[YasserKhalil]

Cross-post at this link
https://www.excelforum.com/excel-programming-vba-macros/1189667-searching-values-in-a-database.html

[SamT]

I know of four ways to do this
Looping thru an array: very very fast
Three finds: Very fast
Find... Find Next: fast
Simple Looping

Array looping Hint

For i = Lbound(arrayOfGroupAndName) to UBound(same array)
If Array(i, 1) = Val1 And Array(i, 2) Val2 Then
Rw = i + 1
Exit For
Next

Find...Find next Hint (Google Find Next)

Set Found = GroupColumn.Find(Val1)
Do While Blah Blah
If Not Found is Nothing and Found.Offset(, 1) = Val2 Then Rw = Found.Row
Blah Blah Blah

Three Finds hint

Dim TopOfGroupNameCel As Range
Dim BottomOfGroupNameCel As Range

Set TopOfGroupNameCel = GroupColumn.Find(What:=Val1, After:=GroupColumn.Cells(Cells.Count).OffSet(,1)
Set BottomOfGroupNameCel = GroupColumn.Find(What:=Val1, After:=TopOfGroupNameCel.OffSet(,-1)
Rw = Range(TopOfGroupNameCel, BottomOfGroupNameCel).Find(Val2).Row

Simple Looping Hint

For i = 1 to Rows.Count
If GroupCol.Cells(i) = Val1 and GroupCol.Cells(i).Offset(, 1) = Val2 Then
Rw = i
Exit For
End If

What I like to do with all my UserForms that access an Excel Database Worksheet is to define Constants that Enumerate the Columns

The easiest method to do this is to Copy the Headers, then PasteSpecial(Values, Transpose) somewhere, then Copy that List and paste it into a Standard Module. Then Edit the list in the module to look like:

Public Enum Sheet1Columns
QuantityAtShop1Col = 1
QuantityAtShop2Col = 2
FoodGroupCol = 3
FoodNameCol = 4
End Enum

Then, assuming you have used logically related Control Names, your code is self checking and reads logically. Ie:

tbxShop1Qty = Sheets1.Cells(Rw, QuantityAtShop1Col)
Those Enumerated Constants are available to all your code and you never have to remember, or look on a sheet for, the column number again.



Please read: What is multiposting? (http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3)