How to loop by chaning the cell reference?
 

Hi, I'm trying to do a loop to add names for each column by using the
following formula. As such, the "C5" in "R1C5" will probably need to
increase from 5 to a previously determined number. Would like seek
expertise how I can loop through this formula effectively. Thank you
very much!


ActiveWorkbook.Names.Add Name:="C5", RefersToR1C1:= _
"=OFFSET('Wfr Lvl'!R1C5,1,0,COUNTA('Wfr Lvl'!C5)-1,1)"

How to loop by chaning the cell reference?
 

i'm not sure what your trying to do from the code, but i think you need to
set a variable

dim iRow as integer

for whatever your loop is to 10 or how ever many times you need to loop
iRow = 5
' your code here
irow = iRow +1
next

then use iRow where you need to increment 5 to 6 or iRow -1 if you need to
got to 4 instead of 6.

maybe someone else will understand more what you're trying to accomplish,
but i thought i'd at least give it a try
--


Gary


wrote in message
oups.com...
Hi, I'm trying to do a loop to add names for each column by using the
following formula. As such, the "C5" in "R1C5" will probably need to
increase from 5 to a previously determined number. Would like seek
expertise how I can loop through this formula effectively. Thank you
very much!


ActiveWorkbook.Names.Add Name:="C5", RefersToR1C1:= _
"=OFFSET('Wfr Lvl'!R1C5,1,0,COUNTA('Wfr Lvl'!C5)-1,1)"

How to loop by chaning the cell reference?
 

its definitely a BAD THING to use names that look like cell references. C5 is
clearly a cell reference and thus you should not use that style as a range
name.
Its not clear what you're trying to selecr as your range. you said each
column? could you be clearer please.

A sheet has n columns of tables. each table is of indeterminate length. the
tables will be call tbl01...tablenn ( assuming less than 100 columns!)

Option Explicit

Sub test()
ClearNames
BuildTable
CreateNames
End Sub
Sub ClearNames()
Dim nm As Name
For Each nm In Names
If Left(nm.Name, 3) = "tbl" Then
nm.Delete
End If
Next
End Sub
Sub BuildTable()
Sheet1.Cells.Clear
Dim col As Long
Dim rw As Long
For col = 1 To Int(Rnd * 20) + 5
With Range(Cells(1, col), Cells(10 + Int(Rnd * 10), col))
.Formula = "=char(64+row())"
.Value = .Value
End With
Next
End Sub

Sub CreateNames()
Dim rw As Long
Dim col As Long
col = 1
Do Until Cells(1, col).Value = ""
rw = Cells(65000, col).End(xlUp).Row
Names.Add "tbl" & Format$(col, "00"), Range(Cells(1, col), Cells(rw,
col))
col = col + 1
Loop
End Sub






" wrote:

Hi, I'm trying to do a loop to add names for each column by using the
following formula. As such, the "C5" in "R1C5" will probably need to
increase from 5 to a previously determined number. Would like seek
expertise how I can loop through this formula effectively. Thank you
very much!


ActiveWorkbook.Names.Add Name:="C5", RefersToR1C1:= _
"=OFFSET('Wfr Lvl'!R1C5,1,0,COUNTA('Wfr Lvl'!C5)-1,1)"

How to loop by chaning the cell reference?
 

Hi, thanks for the fast feedback. Yes, the addnames is a reference and
it is a dynamic range. The intention is to create a chart from a
worksheet where each column is a different series. Then series is
dynamic as data will be added. I am trying to add each column as a
range name to be used in a dynamic chart later.

For example, the whole of column C would be a range using the following
formula,
ActiveWorkbook.Names.Add Name:="C5", RefersToR1C1:= _
"=OFFSET('Wfr Lvl'!R1C5,1,0,COUNTA('Wfr Lvl'!C5)-1,1)"

So I would like to know how to do this for column D, E, F and so on by
looping... yet still keeping the =offset formula to keep the dynamic
range.

How to loop by chaning the cell reference?
 

Something like this...

For r = 5 To NumBins
Range(r, 2).Select
Range(Selection, Selection.End(xlDown)).Select
RangeName = "rngCol" & r
RowName = "'Wfr Lvl'!R1C" & r
ColName = "'Wfr Lvl'!C" & r
' ActiveWorkbook.Names.Add Name:=RangeName, RefersToR1C1:= _
"=OFFSET(RowName,1,0,COUNTA(ColName)-1,1)"
Next r