ANDing 3 criteria|VBA & Macros|Excel Forum|My Online Training Hub

An Excel file has a hand-counted inventory list of drugs: Name, Dosage, Strength, PackageSize, and PackageQty.

The pharmacist batch scans into a SQLExpress Medical Database program free medications received from drug companies. (It is called PAP: Patient Assistance Program.) The computer program automatically generates a 2013 Excel xls file of what she just scanned in. The number and names of the generated columns are always the same. All that varies is the number of rows.

My task is to update the hand-counted inventory with data from the scanned list.

I import the xls file of medications just scanned in, put the data where I want it, and close the file.

Eventually, I get to here:

'Assign Some Variables for What's to Come. Load Each Variable With Data From Successive ‘Rows of the Scanned Data List

For ScannedRow = 2 To ScanLastRow

DrgName = (Cells(Row, 12).Value)

Dose = (Cells(Row, 13).Value)

Strength = (Cells(Row, 14).Value)

Next

This is my problem area. (Please remember I’m an IT guy) But even to me, this looks kludgey. And if, by chance, she scans a medication not in the inventory list what happens? Can ELSE handle the no-match situation?

(The Drug Name, Dosage, and Strength of each medication just scanned in must find a match in an inventory row for the Qty amount of the row to be updated.)

For InventoryRow = 2 To InvLastRow

If DrgName = Range("L2").Value And Dose = Range("M2").Value And  Strength = _Range("N2").Value Then Range("E15").Value = Range("E15").Value + Range("P2").Value

Next