IF LOOP in VBA Function

Question

I made two excel sheet. one contains information as below

Date            Name            Quantity
12/7/2013   Bharat Sapte        12
12/7/2013   Ghorpade            10
14/7/2013   Desmukh             30
17/7/2013   Ghorpade            35

and for second sheet

Name         Date            Date
             12/07/2013
Ghorpade     _**_            _**__

here in ** marked area it should check the condition with first sheet and automatically fill up the Quantity in second sheet

how to make this program for too many entries.

pivot table would be the best method, as @Passerby mentions. Pivot on Name as row header, Date as Column header, and quantity as the value. Simple as that. — Cor_Blimey
– Cor_Blimey, Commented Jul 26, 2013 at 13:23
I see no correlation between the title of the question and what you are asking ... if you're not happy with the comments above (Pivot) or the answer provided (VLOOKUP), please reformulate your question so that it becomes clear where in VBA you are stuck. — MikeD
– MikeD, Commented Jul 29, 2013 at 11:00

MikeD · Accepted Answer · 2013-07-29 11:02:39Z

1

As said in the comments to your question, a Pivot table could do the job. A somewhat more static approach is to

create a "key column" as a string concatenation of date & name before the Quantity column (can be hidden)
use a =VLOOKUP(...) function to retrieve the quantity whereby the search argument is constructed from dates (column headers) and names (row headers) of the result table

screenshot

The formulas in B9 and B10 are

B9: =VLOOKUP(B$8&$A9;$C$2:$D$5;2;FALSE)
B10: =VLOOKUP(C$8&$A9;$C$2:$D$5;2;FALSE)

Mind the usage of $ to make rows (date) and columns (name) absolute to make copying easy; and replace my ";" by "," in the formulas if you use the English locale.

edited Jul 29, 2013 at 11:02

answered Jul 29, 2013 at 10:57

MikeD

8,9412 gold badges32 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

IF LOOP in VBA Function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related