I want to use Match/Index or VLOOKUP in VBA to locate a cell in another worksheet and perform a calculation.

Sequence of Events:

-User will select a Component from a drop-down (Cell B5) and hit an "Overhaul Completed" button
-The button will look up the selected Component in another sheet where all components (A column) are listed with lots of data about them
-The corresponding cell for that component will be selected in the "Hours till Next Overhaul" column (C column).
-For this example, lets say that the cell selected is C5
-The formula for C5 should currently be B5-(PME!G1), where the value imported from PME! is the current engine hour value and it changes every month
- The formula should now change to B5-(PME!G1)-(PME!G1). But the second PME!G1 is going to a static number that will never change. So if the hours are at 14474 this month, the formula should be B5-(PME!G1)-14474.

This is what I have so far:
Dim rngRequested As Range
Set rngRequested = Sheets("Sheet10").Range("B5")
.VLookup(rngRequested.Value, Sheets("Sheet7").Range("A3:C21"), 3, False).Select

The select and vlookup functions are probably used incorrectly here.. So once I have that fixed, I can move onto the calculation part...


The reason why I might seem to be going a bit crazy on the automation is because I want to exploit the full capabilities of excel/vba to make the workbook idiot proof. There will be many operators using this book and our goal is to have them do a little as possible, while getting all sorts of valuable data.