# Thread: Need help with logic

1. ## Need help with logic

Here's the data i have
WX20191204-111905.jpg

That is the code i have
```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
WX20191204-112245.jpg

2. As in the attached?

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

4. 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.

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

6. 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

7. 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:
```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}]

On Error Resume Next
mergeBook.Sheets(rptSheetName).Delete
On Error GoTo 0
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```

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•