What this is doing is choosing cell F2 and putting in the following criteria for our vlookup formula (see “How to do a vlookup” article)
SearchFor RC[-1] refers to the cell that is one column to the left of the cell that the formula is in (so cell E2)
WhereToSearch C[-5]:C[-4] translates to columns A:B where A is 5 columns to the left of the formula cell and B is 4 columns to the left
WhichColumn the second column in the search table, namely column B.
NearOrExact False makes the vlookup search for an exact match
To run the macro, you can either click somewhere between Sub and End Sub and press F5 or you can go back to the Excel worksheet, press Alt F8 to bring up the list of macros, select your MakeVlookupInCell one and press Run.
(2) Instead of using a vlookup formula, why not do the following:
Follow the steps as above to insert a module if you haven’t already.
Type in the following text:
Dim iX As Integer
Dim strSearchString As String
strSearchString = Range(“E2”).Value
iX = 1
Do While Range(“A” & iX).Value <> “”
If Range(“A” & iX).Value = strSearchString Then
MsgBox (strSearchString & “‘s result is ” & (Round(Range(“B” & iX).Value*100, 0) ) & “%”)
iX = iX + 1
What this does is gets the value in cell E2 and then runs down column A until it finds it. When it does find it, it returns the corresponding value in column B, just like a vlookup would do but in a message box with a bit of text on either side of the result. Not a bad alternative.