Consulting

Results 1 to 8 of 8

Thread: Need help with logic

  1. #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
    Please help with my logic! i'm really struggle
    WX20191204-112245.jpg
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,778
    As in the attached?
    Attached Files Attached Files
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

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

  4. #4
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,778
    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.
    Last edited by p45cal; 12-04-2019 at 02:17 PM.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

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

  6. #6
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,058
    Location
    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
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  7. #7
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,778
    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
    Attached Files Attached Files
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #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
  •