PDA

View Full Version : rangename/column#/refersto ISSUES



clvestin
11-14-2005, 01:37 PM
So--I,m trying to name the range (row 4 to row(end of data in column)) for each column in the sheet, giving the range name= row3 of each column of the sheet. Between losing myself in the R1C1 stuff, generally the line for

refersto:= :="=liqfn!R4C" & kol :"R" & fndcol2&"C"&kol, though convoluted ,seems like it should work.

I know a lot of you have been here before. Any helps?








Public Sub tst1()
For i = 1 To Worksheets.Count
Set fndcol = Worksheets(i).Range("b3", "s1000") _
.Cells.SpecialCells(xlCellTypeLastCell)
fndcol2 = fndcol.Row 'Address(xlR1C1)
fndcol3 = fndcol.Column
' fndrng=range(
For kol = 3 To fndcol.Column
'Set blu = Range("r4C" & kol) ',"R"&fndcol2.row&"C"&kol)
Worksheets(i).Names.Add _
Name:="rngnm" & kol, _
RefersToR1C1:="=liqfn!R4C" & kol ':R" & fndcol2&"C"&kol
',"R4C"&fndcol2.column
':"R"&fndcol2.row&"c"&kol)
'Cells(2, kol).Text
' "rngnm" & kol
'& kol :R"&fndcol2&"C"&kol
Next kol
Next i

malik641
11-14-2005, 01:57 PM
As long as NO names repeat...then try this:

Option Explicit
Sub tst1()
On Error Resume Next
Dim ws As Worksheet
Dim iLastCol As Long
Dim iLastRow As Long
Dim i As Long
For Each ws In ActiveWorkbook.Worksheets
With ws
iLastCol = .Cells(3, Columns.Count).End(xlToLeft).Column

For i = 1 To iLastCol Step 1
iLastRow = .Cells(Rows.Count, i).End(xlUp).Row

ActiveWorkbook.Names.Add Name:=.Cells(3, i).Value, _
RefersTo:=Range(Cells(4, i).Address, Cells(iLastRow, i).Address)
Next i
End With
Next ws

End Sub


I like to stay away from R1C1 format. Why bother when you can use regular "A1:C5" format?

Hope this helps!

clvestin
11-14-2005, 05:16 PM
egad...why does it look so simple? why do spend an evening kicking around every possible range definition only to recieve any number of mind numbing errors.

Thank you gratefully for your help.

malik641
11-14-2005, 07:37 PM
egad...why does it look so simple? why do spend an evening kicking around every possible range definition only to recieve any number of mind numbing errors.

Thank you gratefully for your help.
Simplicity is key and what I strive for :thumb

Glad to help!