Using Dynamic Ranges (without range address) inside a Formula

[predzer]

Hey Guys!

I immensely need your help for the below scenario.

I am trying to find a way to address range in a formula. The problem is that, the range is created dynamically. Please refer below image further.

Sketch1.jpg

Here I have 2 tables say Table 1 & Table 2, these tables are snippets of my actual table which has 50 columns & 40 Rows.

Table 1 is generated from a pivot table, and it is dynamic so I wont be having idea of its address (except cell A1 where table 1 starts always)

so Have used the below codes to set range of table 2

Set table2top = Range("A1").End(xlToRight).Offset(0, 1) 
Set table2bot = Range("A1").End(xlToRight).End(xlDown).Offset(0, 1)

Now i have to enter formula =AVERAGEIF($A$1:$I$1,K$1,$A2:$I2) in cell K2 and use autofill for rest of table 2.


I need your help to Replace cell range ($A$1:$I$1,K$1,$A2:$I2) to 'something' as i wont be knowing cell range except starting cell A1.


P.S. I tried naming date row. so i can replace $A$1:$I$1 with a name, but could not figure out for the next part of formula.

I just started using VBA to build a model & Im almost halfway through my model but stuck at this point please help me out

[Jan Karel Pieterse]

I expect VBA generates your table, correct? In that case you should be able to know the size of the table during that process and you can assign a range name to the newly inserted table rows.
Suppose you have a range object called oRng:

oRng.Name = "TheTable"

[predzer]

I expect VBA generates your table, correct? In that case you should be able to know the size of the table during that process and you can assign a range name to the newly inserted table rows.
Suppose you have a range object called oRng:

oRng.Name = "TheTable"

Yes Vba generates my table but I have a table in multiple sheets (22 Sheets) And table range varies in each sheet. So I am looking to code for a single table and loop it through all sheets.

(P.S. all the tables start in same cell (say A1) but varies in rows and columns. Its ok if the code do not take care of columns, i can make number of columns same for all table)

[Jan Karel Pieterse]

This code will create locally defined range names for each sheet which has a name starting with "DataSheet" (adjust accordingly):

Sub AssignRangeNames()
    Dim oSh As Worksheet
    For Each oSh In Worksheets
        If Osh.Name Like "DataSheet*" Then
            oSh.Range("A1").CurrentRegion.Name = "'" & oSh.Name & "'!LocalTable"
        End If
    Next
End Sub

[predzer]

This code will create locally defined range names for each sheet which has a name starting with "DataSheet" (adjust accordingly):

Sub AssignRangeNames()
    Dim oSh As Worksheet
    For Each oSh In Worksheets
        If Osh.Name Like "DataSheet*" Then
            oSh.Range("A1").CurrentRegion.Name = "'" & oSh.Name & "'!LocalTable"
        End If
    Next
End Sub

Thanks Jan Karel Pieterse. But as i mentioned in my first post,

"I need help to Replace cell range ($A$1:$I$1,K$1,$A2:$I2) to 'something' as i wont be knowing cell range except starting cell A1"

​(P.S. I can use your code in the later stage once i get solution to my first post. Thank You)

[Jan Karel Pieterse]

Guess I misunderstood. But you can use the name my code creates to refer to the entire table and use e.g. INDEX(LocalTable,,2) to refer to the second column if that helps?

[georgiboy]

How will you know where to place table2 if you don't know how large table one is?

Where you say the formula is going on column K, will this always be K?

Could you calculate the Average formula with VBA and place the result at the end of the table?

Are we dealing primarily with amending historic data or are we creating a process that creates the correct format going forwards? These are very different things.

[predzer]

Guess I misunderstood. But you can use the name my code creates to refer to the entire table and use e.g. INDEX(LocalTable,,2) to refer to the second column if that helps?

I need to refer entire row from A2:I2 for formula in K2 and autofill for the rest of the table.
(I know A2, but not the end range K2)

[predzer]

How will you know where to place table2 if you don't know how large table one is?

Where you say the formula is going on column K, will this always be K?

Could you calculate the Average formula with VBA and place the result at the end of the table?

Are we dealing primarily with amending historic data or are we creating a process that creates the correct format going forwards? These are very different things.

If you see my first post, I have mentioned that i will be using below code to place table2.

Set table2top = Range("A1").End(xlToRight).Offset(0, 1) 
Set table2bot = Range("A1").End(xlToRight).End(xlDown).Offset(0, 1)

It wont be always K. It will be based on the code above, like

Set table2value = Range("A1").End(xlToRight).Offset(1, 1)

Now i have to enter the average formula in the cell selected using above code and autofill for the entire table.

[georgiboy]

Are you looping through historic sheets or are you setting a new method for going forwards (one sheet per day)?

[predzer]

Are you looping through historic sheets or are you setting a new method for going forwards (one sheet per day)?

Through Historic Sheets.

[georgiboy]

Here is something I have mashed together, is assumes that table2 is made of just headers (dates) and resides in its own sheet.
I have mashed this together as I am not quite sure what you are after, this is what I think you need and should give a better starting point.

Sub FillSheet()    
    Dim sh As Worksheet, tbl2Paste As Range
    Dim endRow As Long
    Dim newTBLrng As Range, rCell As Range
    Dim tmpRng As Range, formulaCell As Range
    
    For Each sh In ThisWorkbook.Worksheets ' loop through all sheets in the workbook
        If Left(sh.Name, 5) = "Sheet" Then ' just somethig I did to exclude the sheet with table2 on
            Set tbl2Paste = sh.Range("A1").End(xlToRight).Offset(, 2) ' set where to paste table2
            endRow = sh.Range("A" & Rows.Count).End(xlUp).Row - 1 ' sets the endrow of table1
            ShTbl2.Range("A1:C1").Copy ' copy table2 from a sheet I renamed
            tbl2Paste.PasteSpecial xlAll ' paste table2 to the sheet
            Set newTBLrng = sh.Range(tbl2Paste, tbl2Paste.End(xlToRight)) ' set the size of the table2 headers
            For Each rCell In newTBLrng.Cells ' loop through the pasted table2 header rows (dates)
                Set tmpRng = sh.Range(rCell.Offset(1, 0), rCell.Offset(endRow, 0)) ' set range below table2 header to end of table1 data
                For Each formulaCell In tmpRng.Cells ' loop through this temp range
                    'have a choice of placing formula of result below
                    'formulaCell.Value = WorksheetFunction.AverageIf(sh.Range(sh.Cells(1, 1), sh.Cells(1, tbl2Paste.Column - 2)), rCell.Value, sh.Range(sh.Cells(formulaCell.Row, 1), sh.Cells(formulaCell.Row, tbl2Paste.Column - 2)))
                    formulaCell.Formula = "=AverageIf(" & sh.Range(sh.Cells(1, 1), sh.Cells(1, tbl2Paste.Column - 2)).Address & "," & rCell.Address & "," & sh.Range(sh.Cells(formulaCell.Row, 1), sh.Cells(formulaCell.Row, tbl2Paste.Column - 2)).Address & ")"
                Next formulaCell
            Next rCell
        End If
    Next sh


End Sub

Hope this helps

[predzer]

Here is something I have mashed together, is assumes that table2 is made of just headers (dates) and resides in its own sheet.
I have mashed this together as I am not quite sure what you are after, this is what I think you need and should give a better starting point.

Sub FillSheet()    
    Dim sh As Worksheet, tbl2Paste As Range
    Dim endRow As Long
    Dim newTBLrng As Range, rCell As Range
    Dim tmpRng As Range, formulaCell As Range
    
    For Each sh In ThisWorkbook.Worksheets ' loop through all sheets in the workbook
        If Left(sh.Name, 5) = "Sheet" Then ' just somethig I did to exclude the sheet with table2 on
            Set tbl2Paste = sh.Range("A1").End(xlToRight).Offset(, 2) ' set where to paste table2
            endRow = sh.Range("A" & Rows.Count).End(xlUp).Row - 1 ' sets the endrow of table1
            ShTbl2.Range("A1:C1").Copy ' copy table2 from a sheet I renamed
            tbl2Paste.PasteSpecial xlAll ' paste table2 to the sheet
            Set newTBLrng = sh.Range(tbl2Paste, tbl2Paste.End(xlToRight)) ' set the size of the table2 headers
            For Each rCell In newTBLrng.Cells ' loop through the pasted table2 header rows (dates)
                Set tmpRng = sh.Range(rCell.Offset(1, 0), rCell.Offset(endRow, 0)) ' set range below table2 header to end of table1 data
                For Each formulaCell In tmpRng.Cells ' loop through this temp range
                    'have a choice of placing formula of result below
                    'formulaCell.Value = WorksheetFunction.AverageIf(sh.Range(sh.Cells(1, 1), sh.Cells(1, tbl2Paste.Column - 2)), rCell.Value, sh.Range(sh.Cells(formulaCell.Row, 1), sh.Cells(formulaCell.Row, tbl2Paste.Column - 2)))
                    formulaCell.Formula = "=AverageIf(" & sh.Range(sh.Cells(1, 1), sh.Cells(1, tbl2Paste.Column - 2)).Address & "," & rCell.Address & "," & sh.Range(sh.Cells(formulaCell.Row, 1), sh.Cells(formulaCell.Row, tbl2Paste.Column - 2)).Address & ")"
                Next formulaCell
            Next rCell
        End If
    Next sh


End Sub

Hope this helps

Thank you so much georgiboy. Well, I have already got a code in place but I can use this code in parts to adjust my code.

As of now Im stuck in entering formula. I have defined date as a named range. I need help in replacing date as a variable.

Dim date As Range
Set date = Range(ref, ref.End(xlToRight))  'ref is another range, this line of code works perfectly
   
Selection.FormulaR1C1 = "=AVERAGEIF(" & date.address & ",R9C,RC" & cp & ":RC" & cd & ")"   'cp & cd are column numbers

here im not able to define date in the formula. I am getting error.

[georgiboy]

You will not be able to define "date" as a range because to Excel date is a Date, as in the date today is...

Try to define it as "tmpDte" or "rngDate" or "dteRng" instead, think of something that means something to you and is also enhancing the ability to read the code in the future

[predzer]

You will not be able to define "date" as a range because to Excel date is a Date, as in the date today is...

Try to define it as "tmpDte" or "rngDate" or "dteRng" instead, think of something that means something to you and is also enhancing the ability to read the code in the future

Thanks georgiboy! Ive changed the variable name now! works smooth

[georgiboy]

You're welcome, glad it helped.