Generating the report using Sumifs

[srlakra]

Hi,

I just wanted to generate the report using the macro but something went wrong. Can any one assist please.
Sheet 1 contains the data. headers are the name of the ranges.

Region Type Pro_Type Amt North Retail 0 20237.00 East Project H 9552.00 West Retail 0 24832.00 South Project R 16111.00 North Retail 0 9192.00 North Retail 0 5765.00 East Retail 0 19960.00 East Retail 0 21645.00 East Project R 19418.00 East Project R 22599.00 West Project C 18368.00 West Project C 14860.00 West Project H 11779.00 West Project R 6304.00 West Project R 9508.00 North Retail 0 3324.00 North Retail 0 13292.00 North Retail 0 17061.00 <colgroup><col width="76" span="4" style="width:57pt"> </colgroup><tbody> </tbody>

<tbody>
</tbody>

Sheet 2

	a	b	c
1
2
3	Select type	This Contains Validation (Retail,Project,H,R,C)
4
5
6
7
8		Region <tbody> </tbody>	Sale <tbody> </tbody>
9		North <tbody> </tbody>
10		East <tbody> </tbody>
11		West <tbody> </tbody>
12		South <tbody> </tbody>

<tbody>
</tbody>

Code that I've used:

Sub rep()


Dim Amt As Range
Dim Region As Range
Dim Pro_Type As Range
Dim I As Integer


For I = 9 To 12


If Cells(3, "B") = "Retail" Then

Cells(I, "C") = worksheetsfunction.SumIfs(Amt, Region, Cells(I, "B"), Pro_Type, "0")

Else

If Cells(3, "B") = "Project" Then

Cells(I, "C") = worksheetsfunction.SumIfs(Amt, Region, Cells(I, "B"), Pro_Type, "<>" & "0")


Else

Cells(I, "C") = worksheetsfunction.SumIfs(Amt, Region, Cells(I, "B"), Pro_Type, Cells(3, "B"))


End If


Next I


End Sub

 

[srlakra]

I have further corrected the code
Sub rep()


Dim Amt As Range
Dim Region As Range
Dim Pro_Type As Range
Dim i As Long


Set Amt = Range("Amt")
Set Region = Range("Region")
Set Pro_Type = Range("Pro_Type")


For i = 9 To 12


If Cells(3, "B") = "Retail" Then

Cells(i, "C") = WorksheetFunction.SumIfs(Amt, Region, Cells(i, "B"), Pro_Type, "0")

Else

If Cells(3, "B") = "Project" Then

Cells(i, "C") = WorksheetFunction.SumIfs(Amt, Region, Cells(i, "B"), Pro_Type, "<>" & "0")


Else

Cells(i, "C") = WorksheetFunction.SumIfs(Amt, Region, Cells(i, "B"), Pro_Type, Cells(3, "B"))


End If


Next i


End Sub

 

[srlakra]

Some one please help

 

[ask2tsp]

I just wanted to generate the report using the macro but something went wrong

something is a bit vague; tell us more. what did you want to do (in english, not in code)? what was the result? what is wrong in the results?

 

[ask2tsp]

I changed the layout of the code using indentation. Every time you start a (if..else..end if / for..next) block, indent the code. At the end of the block unindent. It makes your code so much easier to read.
By doing that I noticed that an 'end if' was missing.
Check the code below.

Option Explicit

Sub rep()

Dim Amt As Range
Dim Region As Range
Dim Pro_Type As Range
Dim i As Long

Set Amt = Range("Amt")
Set Region = Range("Region")
Set Pro_Type = Range("Pro_Type")

For i = 9 To 12
   If Cells(3, "B") = "Retail" Then
      Cells(i, "C") = WorksheetFunction.SumIfs(Amt, Region, Cells(i, "B"), Pro_Type, "0")
   Else
      If Cells(3, "B") = "Project" Then
         Cells(i, "C") = WorksheetFunction.SumIfs(Amt, Region, Cells(i, "B"), Pro_Type, "<>" & "0")
      Else
         Cells(i, "C") = WorksheetFunction.SumIfs(Amt, Region, Cells(i, "B"), Pro_Type, Cells(3, "B"))
      End If
   End If
Next i

End Sub

 

[srlakra]

I want that once i run the macro the report should generate in sheet 2 basis the validation. Like Retail, Project, H, R, C for different regions. Sum of amount.

 

[srlakra]

Thanks a lot it worked.
I was making a silly mistake.