Using Dynamic Ranges (without range address) inside a Formula

1 Attachment(s)

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.

Attachment 22554

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

Code:

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 :banghead: please help me out: pray2:

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:

Code:

oRng.Name = "TheTable"

Quote:

Originally Posted by 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:

Code:

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)

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

Code:

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

Quote:

Originally Posted by Jan Karel Pieterse

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

Code:

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)

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?

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.

Quote:

Originally Posted by 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?

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)

Quote:

Originally Posted by 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.

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

Code:

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

Code:

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.

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

Quote:

Originally Posted by georgiboy

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

Through Historic Sheets.

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.

Code:

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

Quote:

Originally Posted by 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.

Code:

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. :yes

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.

Code:

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.

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

Quote:

Originally Posted by 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

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

You're welcome, glad it helped.