Setting named ranges using a macro

[Joe22]

Hi guys,

I have a named ranged ranged called Trend1Basic which is defined as:
=OFFSET(Data!$B$14;0;0;1;COUNT(Data!$B$14:$AC$14))

Suppose I would want to define Trend2Basic up till Trend15Basic, which are all exactly the same formula but then for rows 15, 16, and so on. What should a macro look like to define these named ranges?

I know very little about macros, except how to run them (and how to use common sense ).

P.S. In reality I have over a 1000 named ranges to define, which is why I'm looking to do this with a macro.

[snb]

[vba]sub snb()
for j=14 to 20
sheets("Data").rows(j).specialcells(2).name="common_sense_" & j
next
end sub[/vba]

[Joe22]

Thanks snb, but how do I make the macro to define the formula for the named range as:
=OFFSET(Data!$B$14;0;0;1;COUNT(Data!$B$14:$AC$14))?

The macro now creates names that refer to cells rather than the above named range.

[snb]

Your formula also refers to cells (otherwise it wouldn't be a named Range )

[Joe22]

I'll try to be more clear. When I run the macro you suggested I get named cells:
common_sense_14 =Data!$G$14
common_sense_15 =Data!$G$15
etc.

What I am looking for is:
common_sense_14 =OFFSET(Data!$B$14;0;0;1;COUNT(Data!$B$14:$AC$14))
common_sense_15 =OFFSET(Data!$B$15;0;0;1;COUNT(Data!$B$15:$AC$15))
etc.

[snb]

It only means the cells A14:F14 and the cell H14 are empty (or contain formulae)
if that is the case the Excel formula will also produce an unexpected result.
It would have been much wiser to post a sample of your workbook.

An alternative could be:

[VBA]Sub snb()
For j=14 To 20
sheets("Data").cells(j,2).resize(sheets("Data").cells(j,columns.count).end( xltoleft).column-1).name="common_sense_" & j
Next
End Sub [/VBA]

[Joe22]

I guess I still wasn't completely clear. The result should be that when you go to the name manager after executing the macro, I have named ranges with the exact formulas as I defined before. This is irrespective of the Excel document, which could in fact be completely empty at this point in time.
The macros you suggest instead seem to execute the formulas I need rather then enter the formulas themselves into the name manager. Given that the data in the Excel sheet might change in the future, this is not what I'm looking for.

[snb]

The best way to use dynamic ranges is to ue VBA.
If you are not familiar to VBA there's a second best solution using named ranges and sometimes confusing formulae like you are using.
Since you are asking for a VBA solution you better turn to VBA completely.
In that case you can use any dynamic range (not a named range) every moment you wish.

[Bob Phillips]

[VBA]Const FORMULA_NAME As String = "=OFFSET(Data!$B$<row>,0,0,1,COUNT(Data!$B$<row>:$AC$<row>))"
Dim i As Long

For i = 14 To 28

ActiveWorkbook.Names.Add Name:="Trend" & i - 13 & "Basic", _
RefersTo:=Replace(FORMULA_NAME, "<row>", i)
Next i
[/VBA]

[mikerickson]

In reality I have over a 1000 named ranges to define

a named range for each row of an over 1000 row range!!!!

I see the potential for a lot of formulas like =INDIRECT("Trend" & A1 & "Basic)

The volatile INDIRECT, plus over 1000 names that uses the volatile OFFSET, results in a very slow worksheet.

It sounds like a re-design is in order.

1) you could use a non-volatile definition for your base range.
Name:Trend1Basic
RefersTo: =Data!$B$14:INDEX(Data!$14:$14, 1, 14+COUNT(Data!$B$14:$AC$14))

2) you might make the name a relative reference:
select a cell in row 14 and make the definition
Name: TrendThisRow
RefersTo: =Data!$B14:INDEX(Data!14:14, 1, 14+COUNT(Data!$B14:$AC14))

3) Change the (hypothetical) INDIRECT to a non-volatile
Instead of =INDIRECT("Trend" & A1 & "Basic) use

=INDEX(Data!$B:$B, A1+13,1) :INDEX(Data!$B:$AD, A1+13, COUNT(INDEX(Data!$B:$AC, A1+13, 0)))