Process Automation

[Kusini]

I am looking for a way to automate this process. The table in the file marked as Actual depends on the first two tables Vol and Budget. I need an idea of how to automate the process.

Here is a sample file - https://www.box.com/files#/files/0/f/0/1/f_2514961751.

 

[Mohammad Basem]

You know Kusini, I was trying to make you talk about what you need to achieve exactly. We are moving.

Now, it is clear that you want a macro to fill the formulae for you in the first column and reference rows in the table Actuals. Is that all?

 

[Kusini]

Yes thats what I am looking for a macro that will identify the reference cell/row "S" and fill in the formula for the rows under each reference

 

[Mohammad Basem]

Ok, that’s clear now let us talk about the three tables.

The tables start at row 9, each table has 15 columns and there is a single blank column separating each table from the other. Is this layout fixed or might change? If anything might change, then please explain how?

…identify the reference cell/row "S" and fill in the formula for the rows under each reference

In table Actuals, the formulae are in the "S" reference rows and the first column only (titled 001). Does your comment mean that the Table Actuals is a table of formulae and every single cell shall have a formula? Please explain.

It is worth to mention that the "S" row in tables Vol and Budget has numbers rather than formulae. I guess it is the sum of the numbers underneath down to the next "S" reference. If this is the case then it is recommended using formulae to sum the numbers rather than writing the sum by yourself to avoid errors.

 

[Kusini]

Here is an updated table showing the input and the desired output. I think this makes it easier to see the picture on what process is done and what I aim to achieve.

https://www.box.com/s/b3ce883672eba986ad41

To answer the questions on possible changes.
1. The table will always start at row 9 and this will not change.
2. The number of columns needs to be flexible and there will always be a blank column to separate the table. The ideal number of columns should be 12 to represent one fiscal year, but since I am working on data from 2011 and Q1 2012 that's why I have 15 columns. If this remains flexible it will be beneficial since a times I may need to do only a slice say 6 months report or 9 months report, so the tables should be as dynamic as possible with the blank row showing where the tables are separated.
3. The table Actual is the desired output table and this is what I aim to automate. Thus the row with heading 001, 002, 003 just indicates the month and as such will not require a formula its just a reference showing where the data is coming from based on Table Vol and Table budget.
4. The row S in Vol and Budget is always provided and is used as a reference for the formula and will not need to be calculated.

Let me know if you need any further clarification.

Regards,

Kusini

 

[Mohammad Basem]

Try this,

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Option Explicit

Sub PrepareActuals()

    Dim iActualsStartCol As Integer, _
        iActualsEndCol As Integer, _
        iVolStartCol As Integer, _
        iBudgetStartCol As Integer
    Dim lLastRow As Long, _
        lFirstRefRow As Long, _
        lLastRefRow As Long
    Dim sFormula As String
    Dim rProcess As Range, rCell As Range

[COLOR="Green"]    '*
    '* Define required data block boundries and[/COLOR]
    iVolStartCol = 4
    iBudgetStartCol = Range("A11").End(xlToRight).End(xlToRight).Column
    iActualsStartCol = Cells(11, Columns.Count).End(xlToLeft).End(xlToLeft).Column
    iActualsEndCol = Cells(11, Columns.Count).End(xlToLeft).Column
    lLastRow = Range("C" & Rows.Count).End(xlUp).Row
[COLOR="Green"]    '*
    '* Fill table Actuals with formulae[/COLOR]
    Application.ScreenUpdating = False
    Set rProcess = Range("A11:A" & lLastRow)
    Set rCell = rProcess.Find(what:="*")
    Do While rCell.Address(False, False) <> "A11"
        lFirstRefRow = rCell.Row
        Set rCell = rProcess.FindNext(rCell)
        lLastRefRow = rCell.Row - 1
        If lLastRefRow < lFirstRefRow Then      [COLOR="Green"]'* Search wrapped around[/COLOR]
            lLastRefRow = lLastRow
        End If
        sFormula = "=" & Cells(lFirstRefRow, iVolStartCol).Address(False, False) _
            & "/" & Cells(lFirstRefRow, iVolStartCol).Address(True, False) _
            & "*" & Cells(lFirstRefRow, iBudgetStartCol).Address(True, False)
        With Range(Cells(lFirstRefRow, iActualsStartCol), Cells(lLastRefRow, iActualsEndCol))
            .Formula = sFormula
            .Font.Bold = False
            .Rows(1).Font.Bold = True
        End With
    Loop
    Application.ScreenUpdating = True
[COLOR="Green"]    '*
    '* Clean-up[/COLOR]
    Set rCell = Nothing
    Set rProcess = Nothing

End Sub[/COLOR][/SIZE][/FONT]

 

[Kusini]

Hi Mohammad,

I have tested it and it works like a charm on the sample. I will test it on the large file and see the results.

Thanks

 

[Mohammad Basem]

Al-hamdullah, thank you for the feedback!

 

[Kusini]

Hi Mohammad,

I have tested the macro on a larger sample data and I just have one request. The current macro refers to the first process column as its reference column, only challenge is that at times a process may have two or multiple assigned units and each unit has a sum row S. Like I have indicated in the new file provided below. How do I adjust the macro so that the unit column B becomes the reference column and not the process column A?

Link: https://www.box.com/s/659ead18bbe6cf408c28

Other than this the macro is very flexible especially on reducing or increasing the columns desired it worked perfectly.

Thanks,

Kusini.

 

[Mohammad Basem]

Here you go,

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Option Explicit

Sub PrepareActuals()

    Dim iActualsStartCol As Integer, _
        iActualsEndCol As Integer, _
        iVolStartCol As Integer, _
        iBudgetStartCol As Integer
    Dim lLastRow As Long, _
        lFirstRefRow As Long, _
        lLastRefRow As Long
    Dim sFormula As String
    Dim rUnit As Range, rCell As Range

[COLOR="Green"]    '*
    '* Define required data block boundries and[/COLOR]
    iVolStartCol = 4
    iBudgetStartCol = Range("A11").End(xlToRight).End(xlToRight).Column
    iActualsStartCol = Cells(11, Columns.Count).End(xlToLeft).End(xlToLeft).Column
    iActualsEndCol = Cells(11, Columns.Count).End(xlToLeft).Column
    lLastRow = Range("C" & Rows.Count).End(xlUp).Row
[COLOR="Green"]    '*
    '* Fill table Actuals with formulae[/COLOR]
    Application.ScreenUpdating = False
    Set rUnit = Range("B11:B" & lLastRow)
    Set rCell = rUnit.Find(what:="*")
    Do While rCell.Address(False, False) <> "B11"
        lFirstRefRow = rCell.Row
        Set rCell = rUnit.FindNext(rCell)
        lLastRefRow = rCell.Row - 1
        If lLastRefRow < lFirstRefRow Then      [COLOR="Green"]'* Search wrapped around[/COLOR]
            lLastRefRow = lLastRow
        End If
        sFormula = "=" & Cells(lFirstRefRow, iVolStartCol).Address(False, False) _
            & "/" & Cells(lFirstRefRow, iVolStartCol).Address(True, False) _
            & "*" & Cells(lFirstRefRow, iBudgetStartCol).Address(True, False)
        With Range(Cells(lFirstRefRow, iActualsStartCol), Cells(lLastRefRow, iActualsEndCol))
            .Formula = sFormula
            .Font.Bold = False
            .Rows(1).Font.Bold = True
        End With
    Loop
    Application.ScreenUpdating = True
[COLOR="Green"]    '*
    '* Clean-up[/COLOR]
    Set rCell = Nothing
    Set rUnit = Nothing

End Sub[/COLOR][/SIZE][/FONT]

 

[Kusini]

Thanks so much Mohammad, its working perfectly now.