Sum Column B based on Column A using Excel VBA Macro

[Rakesh Kamani]

There are 4 worksheets in the workbook. Command button in sheet 1, project name and actual hours in sheet 2, project name and planned hours based on month and year in sheet 3, final report display on sheet 4. (Name of project, actual hours and planned hours).

When I click on the Command button on Sheet 1, Report Display on Sheet 4 based on Sheet 2 and Sheet 3.

How to write a VBA in this case?

Thank you in advance.

 

[DanteAmor]

Hi and welcome to MrExcel

You could put examples of your sheets 2 and 3.
How do you want the result on sheet 4, showing the previous examples.
And how many records do you have on sheets 2 and 3?

 

[Rakesh Kamani]

Hi and welcome to MrExcel

You could put examples of your sheets 2 and 3.
How do you want the result on sheet 4, showing the previous examples.
And how many records do you have on sheets 2 and 3?

Thank you for your quick response.

I have unable to attached excel document, so I'm attached snap shot for your reference.

 

[DanteAmor]

Please help me with your examples, I can't capture them from an image.
You can put them with the xl2bb tool.
And you must explain what you need, your examples in image 2 do not match image 1 and image 3 is empty.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[Rakesh Kamani]

Please help me with your examples, I can't capture them from an image.
You can put them with the xl2bb tool.
And you must explain what you need, your examples in image 2 do not match image 1 and image 3 is empty.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

1)sheet1: I added command button(Generate report) # When I click on button, the report display in sheet4 - based on sheet2 and sheet3

	Generate report

2)Sheet2Actual Hours) In this sheet we can consider Project Name and Hours columns only, ignore rest of columns.

Project Name	product	Division	Hours	Phase
M312101	Key	TD-Pu	4	1
M309401	Doors	TDP	6	2
M309201	Metal	TDS	3	3
M309401	Iron	MR	4	4
M312101	Metal	EX	2	5

3)Sheet3: (Planned Hours): we can consider from march to june(2020/03 to 2020/06)

	2020/01	2020/02	2020/03	2020/04	2020/05	2020/06
M327905-A M SYC SBW	138.50	125.62	152	140	125.32	362.14
M309201-A M SYC SBW	25	65	85	32.50	145.63	85
M309401-A MM SYMC SBW	145.32	36.14	23.56	125	365	12
M327905-A M SYC SBW	25.23	365	45.12	21.4	25.36	21.3
M312101- SYMC SBW	54.3	25.63	241	2.53	3	14.23
M327905-A MM_2	25.3	23.1	25.36	45.85	78.06	65

4)Sheet4: (Final Report)# Currently I filled manually for your reference.

Project Name	Actual Hours	Planned Hours
M312101	6	260.76
M309401	10	525.56
M309201	3	348.13
M327905		892.64
M327905-A MM_2		214.27
Total	19	2241.36

Thank you in advance

Thanks,
Rakesh Kamani

 

[DanteAmor]

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[MARK858]

Hi DanteAmor, I know that uploading to a file sharing site would be better but the data in post number 5 can be copied (At least I can). Column and row references might be an issue though..

 

[DanteAmor]

Column and row references might be an issue though..

Hi Mark, thanks for your comments.

In addition to the fact that rows and columns are not visible in the ranges, I see other problems.
In the pictures:
- It has merged cells.
- The projects are in a row and apparently the amount to be added is in another row.
- According to the example of ranges should only add from March to June, but I do not see where to get that range of months.

I am inclined to take the data from the images since the ranges do not look real. I make the first proposal and see how it works.

 

[DanteAmor]

Hi @Rakesh Kamani.

The date on sheet 2 is a real date or text.
How can I know which dates to add?
In your image periods 12, 01, 02, 03 are merged in 2 columns, but 04 is merged in 3 columns, is that so?
Any particular reason?

 

[DanteAmor]

Hi @Rakesh Kamani.

Sorry, but I have more doubts.
In your image, you have the M327905 project, it has 2 values: 47.87 and 31.58, But in that group I also see the value: 67.50.
But I also see a total, which does not seem like a value: 139/136.

You could comment, for that project which is the result you need.