PDA

View Full Version : [SOLVED] Need help with logic



geniezzzz
12-04-2019, 11:10 AM
Here's the data i have
25530

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
Please help with my logic! i'm really struggle
25531

p45cal
12-04-2019, 01:13 PM
As in the attached?

geniezzzz
12-04-2019, 01:23 PM
It's required to use VBA Macro to complete this report, i wish i could use Pivot table

p45cal
12-04-2019, 01:42 PM
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.

geniezzzz
12-04-2019, 01:59 PM
yeah, it's assignment. we have to use VBA and if else statement to accomplish this

SamT
12-05-2019, 10:41 AM
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

p45cal
12-05-2019, 04:07 PM
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 }]

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

geniezzzz
12-05-2019, 04:39 PM
Thank you so much!!
one more question, what is Srce for?