CODEWITHSUNDEEP
vbaexcel
Samuel Hulla
Samuel Hulla

Reputation: 7089

Creating a summary table (how to process data through a dynamic for loop for specific column and row)

Pretty new to macros vba, so it's kinda multiple questions in one
What I'm trying to create is a summary. Let's say I have a huge data table in excel, for clearness sake here's an excerpt (only for illsutration, actual table is much larger)

Chase_list

  A (Name)         B (Due)    C (Queries)     D (Comment)      
  Customer 1       50 464     20 000          Long-time debtor
  Customer 2        3 159     0               Resent invoice
  Customer 1       15 000     0               
  Customer 4       18 000     3 200           Promised payment

So let's say I got this set of data, now I want to create a function that will go through every cell in the table and if the sum of the due and queries (column B and C) is greater than 20 000 to write the company into a summary table (on a new worksheet)

Here's what I have so far

Private Sub CommandButton1_Click()

  Dim chase_list As Worksheet
  Dim summary_list As Worksheet
  Dim due As Range
  Dim query As Range
  Set chase_list = Sheets("Chase_list") ' the name of my data worksheet in example
  Set summary_list = Sheets("Summary_list") ' the name the sheet i want to output data to

  ' now i need to create a function that will add the query and due column together
  Dim current_row_sum As Range

  'Let's go through every due invoice and query
  For Each due In chase_list.UsedRange.Columns("B")
    For Each query In chase_list.UsedRange.Columns("C")
        current_row_sum = WorksheetFunction.Sum(due, query)
        If (current_row_sum >= 20000) Then
            ' now i somehow need to create a loop that will basically add a new line every time a value is added, but I do not know how
            ' I know I could set it through Range("").Value = field, issue is, I don;t want to put it in a stationary cell, but on new line for each new value
        End If
    Next query
   Next due


End Sub

There are probably more issues with the code, given I'm new to vba, albeit I have some coding experience

Expected output

Summary_list

  A (Name)         B (Total)   C (Comment)        
  Customer 1       70 464      Long-time Debtor               
  Customer 4       21 200      Promised Payment

I know it's not really a specific answer, sorry for that, but it's kind of difficult to split this into parts. Thank you in advance

Upvotes: 1

Views: 263

Answers (1)

Xabier
Xabier

Reputation: 7735

How about something like the following, this assumes you already have a second sheet for your summary:

Sub foo()
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
Dim wsResult As Worksheet: Set wsResult = Sheets("Sheet2")
'declare and set your worksheet, change as required
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'find the last row with data on column A

For i = 2 To LastRow
'loop from row 2 to last
    If ws.Cells(i, 2) + ws.Cells(i, 3) > 20000 Then 'if Column 2 (ie B) + Column 3 (ie C) > 20000 then
        ResultFreeRow = wsResult.Cells(wsResult.Rows.Count, "A").End(xlUp).Row + 1 'find the next free row on Result Sheet
        ws.Rows(i).Copy wsResult.Rows(ResultFreeRow) 'copy row to next free row
    End If
Next i

End Sub

Upvotes: 1

Related Questions