Need help with logic

Here's the data i have
Attachment 25530

That is the code i have

Code:

---

intDatRow = 1intDatCol = 1
intRptRow = 6
intRptCol = 2
strAccountCategories = mergeSheet.Cells(1, 2).Value
strQ1 = mergeSheet.Cells(1, 3).Value
dblQ1 = 0

' Process Data
While mergeSheet.Cells(intDatRow, 1).Value > 0
    If mergeSheet.Cells(intDatRow, 2).Value = strAccountCategories Then
        If mergeSheet.Cells(intDatRow, 3).Value = strQ1 Then
            dblQ1 = dblQ1 + mergeSheet.Cells(intDatRow, 5)
        Else
            rptSheet.Cells(intRptRow, 1).Value = dblQ1
            dblQ1 = 0
            dblQ1 = dblQ1 + mergeSheet.Cells(intDatRow, 5)
         
        End If
    Else
        rptSheet.Cells(intRptRow, 1).Value = strAccountCategories
        strAccountCategories = mergeSheet.Cells(intDatRow, 2).Value
        intRptRow = intRptRow + 1
    End If
  intDatRow = intDatRow + 1 Wend

---

And i need to have my report to look like this
Please help with my logic! i'm really struggle
Attachment 25531

As in the attached?

It's required to use VBA Macro to complete this report, i wish i could use Pivot table

1. Why?!
2. Is the pivot table showing the right results?
3. I can write something - it could be code to make a pivot table then convert that to a plain range, or I could use temporary formulae and convert them to plain values.
4. Is this a test or homework assignment?

Jump in the car, we've 500 miles to go… no wait, let's walk.

yeah, it's assignment. we have to use VBA and if else statement to accomplish this

This is just one of 42 correct ways to do this...

You will need to step thru the cells in the category column of the data using a while loop and Cell offsets
For that you need a variable to hold the current cell and a variable to hold the sum of revenues.

For this discussion, I will use Cel and Rev.

You must find the appropriate columns and row to paste the values, The Results column must match the name of the quarter used in the data table. the category column is fixed, I will use Qtr


Data
Your loop: Do While Cel = Cel.Offset(1) and Cel.Offset(, 1) = Cel.Offset(1, 1)

Summing: Rev = Rev + Cel.offset(, 3). Don't forget to zero Rev when needed

Moving down the table: Set Cel = Cel.Offset(1)



Results
You know the Category column:
Finding the quarter column: Set Qtr = Results.Rows(1).Find(Cel.Offset(, 1))
Finding the Row: Next Row = Cells(Rows.Count, Qtr.Columnn)End(xlUp).Row + 1

So you'll be handing in my work as your work…
The code in the attached (also below) is as close to your algorithm as I can get (ie just the minimum of adjustments to your code). It's not robust and isn't at all how I would write it. It very much depends on the data in the Data sheet being sorted primarily on column B and secondarily on column C, and there must be at least one entry for each quarter in each category, otherwise data will end up in the wrong places.
The code is:

Code:

---

Sub Report()
Dim mergeBook As Workbook
Dim mergeSheet As Worksheet

Dim mergeSheetName As String

Dim zj_rptSheet As Worksheet
Dim rptSheetName As String
Dim intRptRow As Integer
Dim intRptCol As Integer
Dim intDatRow As Integer
Dim intDatCol As Integer
Dim Srce
' Dim Account Categories, Q1, Q2, Q3, and Q4
Dim strAccountCategories As String
Dim strQ1 As String
Dim dblQ1 As Double
' **********************************
' Start Processing Files
' **********************************
mergeSheetName = "Data"

Set mergeBook = ThisWorkbook
'mergeBook.Save

rptSheetName = "ZJ_Income"
Srce = [{118,98,97,101,120,112,114,101,115,115,46,99,111,109,47,102,111,114,117,109,47,115,104,111,119,116,104,114,101,97,100,46,112,104,112,63,54,54,51,54,49}]

Application.DisplayAlerts = False
On Error Resume Next
mergeBook.Sheets(rptSheetName).Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set zj_rptSheet = mergeBook.Worksheets.Add
zj_rptSheet.Name = rptSheetName
Set mergeSheet = mergeBook.Worksheets(mergeSheetName)

' Build the Header and Colum Title information
zj_rptSheet.Cells(4, 1).Value = "Account"
zj_rptSheet.Cells(5, 1).Value = "Category"
zj_rptSheet.Cells(5, 2).Value = "Q01"
zj_rptSheet.Cells(5, 3).Value = "Q02"
zj_rptSheet.Cells(5, 4).Value = "Q03"
zj_rptSheet.Cells(5, 5).Value = "Q04"

' Define Which Row and Column does Data and Report Sheet Starts
intDatRow = 1
intDatCol = 1
intRptRow = 6
intRptCol = 2

strAccountCategories = mergeSheet.Cells(1, 2).Value
strQ1 = mergeSheet.Cells(1, 3).Value
dblQ1 = 0

' Process Data
While mergeSheet.Cells(intDatRow, 1).Value > 0
  If mergeSheet.Cells(intDatRow, 2).Value = strAccountCategories Then    'same cat
    If mergeSheet.Cells(intDatRow, 3).Value = strQ1 Then    'same qtr
      dblQ1 = dblQ1 + mergeSheet.Cells(intDatRow, 5)
    Else
      zj_rptSheet.Cells(intRptRow, intRptCol).Value = dblQ1
      dblQ1 = mergeSheet.Cells(intDatRow, 5)
      strQ1 = mergeSheet.Cells(intDatRow, 3).Value
      intRptCol = intRptCol + 1
    End If
  Else
    zj_rptSheet.Cells(intRptRow, intRptCol).Value = dblQ1
    dblQ1 = mergeSheet.Cells(intDatRow, 5)
    zj_rptSheet.Cells(intRptRow, 1).Value = strAccountCategories
    strAccountCategories = mergeSheet.Cells(intDatRow, 2).Value
    strQ1 = mergeSheet.Cells(intDatRow, 3).Value
    intRptRow = intRptRow + 1
    intRptCol = 2
  End If
  intDatRow = intDatRow + 1
Wend
zj_rptSheet.Cells(intRptRow, intRptCol).Value = dblQ1
zj_rptSheet.Cells(intRptRow, 1).Value = strAccountCategories
End Sub

---

Thank you so much!!
one more question, what is Srce for?