Converting Countifs formula to VBA

[Mohammed Waseem]

Dear Reader,

How I can write VBA program - I am beginner.

I had created an excel sheet with two sheets "Sheet 1 for filling reporting the data" "Sheet 2 for counting the filled or reported data".

I had used following formula - =COUNTIFS('EHS Walk & Talk Report Status'!B2:B1000,"W1",'EHS Walk & Talk Report Status'!C2:C1000,"Oct",'EHS Walk & Talk Report Status'!D2:D1000,"FY17-18",'EHS Walk & Talk Report Status'!M2:M1000,"B # 25",'EHS Walk & Talk Report Status'!O2:O1000,"Outstanding")

Criteria range will remains the same but criteria will change like following

[TABLE="width: 504"]
<tbody>[TR]
[TD]Weeks[/TD]
[TD]Months[/TD]
[TD]Fiscal Year[/TD]
[TD]Budget Units[/TD]
[TD]Remarks[/TD]
[TD]condition[/TD]
[/TR]
[TR]
[TD]W1[/TD]
[TD]Oct[/TD]
[TD]FY 17-18[/TD]
[TD]B # 25[/TD]
[TD]Outstanding[/TD]
[TD]Unsafe Act[/TD]
[/TR]
[TR]
[TD]W2[/TD]
[TD]Nov[/TD]
[TD] [/TD]
[TD]B # 50[/TD]
[TD]Rectified[/TD]
[TD]Unsafe Condition[/TD]
[/TR]
[TR]
[TD]W3[/TD]
[TD]Dec[/TD]
[TD] [/TD]
[TD]B # 51 & 81[/TD]
[TD] [/TD]
[TD]Rectified[/TD]
[/TR]
[TR]
[TD]W4[/TD]
[TD]Jan[/TD]
[TD] [/TD]
[TD]B # 52[/TD]
[TD] [/TD]
[TD]Outstandings[/TD]
[/TR]
[TR]
[TD]W5[/TD]
[TD]Feb[/TD]
[TD] [/TD]
[TD]B # 52-HGPR[/TD]
[TD] [/TD]
[TD]Good Practice[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Mar[/TD]
[TD] [/TD]
[TD]B # 60[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Apr[/TD]
[TD] [/TD]
[TD]B # 61[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]May[/TD]
[TD] [/TD]
[TD]MFS- Tool Depot[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Jun[/TD]
[TD] [/TD]
[TD]B # 72[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Jul[/TD]
[TD] [/TD]
[TD]B # 73[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Aug[/TD]
[TD] [/TD]
[TD]B # 75[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Sep[/TD]
[TD] [/TD]
[TD]B # 76 [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]B # 77[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]B # 79[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]B # 80[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]B # 90[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]B # 92[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Al Majal[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]All Dept[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]PS CD[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col span="2"><col><col><col><col></colgroup>[/TABLE]

 

[Joe4]

Welcome to the Board!

You can use COUNTIFS in VBA, you just call it like this:

Application.WorksheetFunction.Countifs(...)