Excel VBA Programming Using the table of product sales per m

Excel VBA Programming

Using the table of product sales per month prompt user for a product name and a month number, use INDEX to search for the amount sold of the product and month.

Solution

If this is the sheet layout then the code will be,

   A   B   C   D   E   F
1   Product   Jan   Feb   Mar   Apr   May
2   staplers   72   46   72   26   27
3   paper   37   24   84   69   49
4   pencils   58   89   80   94   46
5   pens   91   21   41   90   77
6   calendars   42   75   39   32   88
7   binders   20   78   25   50   24
8   hole punchers   89   70   71   54   43
9   tape   78   31   73   77   23
10   scissors   42   38   79   33   54
11   rulers   94   99   38   48   72

Function getcell()
With activecell
   cl As string
    rw As string
   InputBox(\"Enter month Name\", \"Enter Column \" )
   InputBox(\"Enter a Product Name\", \"Enter Row \")
  
r = .columns(\"A\").find(rw).row
c = .rows(\"1\").find(cl).Column

If r = nothing or c = nothing then exit function
Set getcell = .cells(r, c)
End with

End function