Vba code to create table

[ANTONIO1981]

HI all

i need to automate the creation of the following table "tab R_conveyor_table"

How is calculated?:

Each site in "tab Volumes" has a month and amount

we need to multiply in each month: each site volume, per programme %, per price, in "tab R_conveyor"

example site 1 Amount in January = 4702.6 * 59% *3.75 = 10,404 (cell E2 in Volumes* cell C2 in R_conveyor* Q2 in R_conveyor)

the number of rows in tab volumes and R_conveyor can increase substantially

Shareable link:

QUESTION TO MR EXCEL_CREATE CONVEYOR TABLE.xlsb - Google Drive

thanks in advance

Anthony

 

[RickXL]

Hi,

I create a macro that performs the calculations and leaves the data in a new worksheet called "tmp". You will need to create that to try the code - or change the name in the code (marked in red).

Sub MakeTable()
    Dim vol     As Variant
    Dim con     As Variant
    Dim tbl     As Variant
    Dim i       As Long
    Dim j       As Long
    Dim iRow    As Long
    Dim dic     As Object

    With ThisWorkbook
        With .Worksheets("VOLUMES")
            vol = .Range("A2").Resize(.Cells(.Rows.Count, "A").End(xlUp).Row, 5)
        End With
        With .Worksheets("R_conveyor")
            con = .Range("A1").Resize(.Cells(.Rows.Count, "A").End(xlUp).Row, 28)
        End With
    End With
    
    Set dic = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(vol)
        dic(vol(i, 2) & "|" & vol(i, 4)) = vol(i, 5)
    Next
    
    ReDim tbl(1 To UBound(con) * 12, 1 To 4)
    For i = 1 To UBound(con) - 1
        For j = 3 To 14
            iRow = (i - 1) * 12 + j - 2
            tbl(iRow, 1) = con(i + 1, 1)
            tbl(iRow, 2) = con(1, j)
            tbl(iRow, 3) = con(i + 1, 2)
            tbl(iRow, 4) = dic(con(i + 1, 1) & "|" & con(1, j)) * con(i + 1, j) * con(i + 1, j + 14)
        Next
    Next

    Application.ScreenUpdating = False
    With ThisWorkbook
        With .Worksheets("tmp")
            .Cells.Clear
            .Range("A1:D1") = Array("Site", "Month", "Programme", "Amount")
            .Range("A2").Resize(UBound(tbl, 1), UBound(tbl, 2)) = tbl
            .Columns("B:B").NumberFormat = "mmm-yy"
            .Columns("D:D").NumberFormat = "_-* #,##0_ -;-* #,##0_ -;_-* ""-""?_-;_-@_-"
            .Columns("A:D").EntireColumn.AutoFit
        End With
    End With
End Sub

The code reads in the VOLUMES worksheet data into an array then copies that to a dictionary so that the values van be looked up based on site and month.
It then reads in the control worksheet into an array called con.
The output array can then be created because the size is known.
The con array is looped round and the data is written to tbl which is eventually copied into the tmp worksheet.
Some formatting is applied. You may need to add some.
Worksheet tmp is cleared prior to use.

I am not sure whether you meant table as in tabular data in a worksheet or as in listobject. I assumed the former.

It should run quite quickly but if you have too much data it could run out of memory - but you do need quite a lot of data to run out.


Regards,

 

[ANTONIO1981]

many thanks ! it works perfect, very impressive!!

the only thing in formatting it showing a weird hyphen on the number (10,404-)

how can i modify this to accounting format , no decimals

.Columns("D:D").NumberFormat = "_-* #,##0_ -;-* #,##0_ -;_-* ""-""?_-;_-@_-"

thanks in advance

Antonio

 

[RickXL]

Hi,

Not sure what to suggest, it works OK for me.

I used the macro recorder to find out what the format settings should be. You could try that and paste in what it gives you to see if that makes a difference.

Plan B. I have just re-recorded the macro and this time I have a different result. Instead of using a minus sign for negative numbers it puts them in parentheses. I have no idea why.

The result was:

.Columns("D:D").NumberFormat = "_(* #,##0_ );_(* (#,##0);_(* ""-""_);_(@_)"

Try that instead.


Regards,

 

[ANTONIO1981]

Many thanks for everything you are a star!

 

[RickXL]

I'm glad we got it working and thanks for the appreciation!


Regards,

 

[ANTONIO1981]

how the code will look if we remove (don't take into account) tab volumes?

question to mr Excel create jet_wash table.xlsb - Google Drive

Originally there was 3 worksheets


- volumes
- R_conveyor
- R_conveyor_table

now we have only 2

- R_Jetwash
- R_Jetwash_table

what the macro needs to calculate is the revenue in each site in each month and create the table that you have in - R_Jetwash_table which shows all sites & all months costs

For example Revenue in Jan 18 in site 32 =

4000 ( tab=R_Jetwash cell= c12)*0.83 ( tab=R_Jetwash cell= Q12) =332

the number of rows in R_jetwash might increase substantially

thanks in advance

Anthony