Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using named cells in relative references
I have a userform which allows people to select a column and places tha value in cell iv65536. I have then named this cell as WhichColumn. Th column as a reference changes regularly and I used to have to go int the code to change the offset value. It is part of an IF statement Old code Else 'if the charge exists ActiveCell.Offset(0, 23).Range("A1").Select ActiveCell.Range("A1,F1").Select Selection.Copy Worksheets("Sheet2").Select c.Offset(0, 1).Select ActiveSheet.Paste Worksheets("Sheet1").Select ActiveCell.Offset(1, -23).Range("A1").Select vSourceCharge = ActiveCell.Value End If What i want to do is set the column reference to refer to the value i cell iv65536 which has the value eg Sheet1!$S:$S. Does anyone know how to refer to a named range or get the contents o that cell and use in a relative reference? thank -- rbekka3 ----------------------------------------------------------------------- rbekka33's Profile: http://www.excelforum.com/member.php...fo&userid=1403 View this thread: http://www.excelforum.com/showthread.php?threadid=27442 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using named cells in relative references
note:
if you place a value in IV65000 the UsedRange of the sheet will be expanded to include that cell. = filesize and memory consumption will increase dramatically. I would place the it in a variable (or store it in a name as a constant) Dim iCol% iCol=inputbox("Column?) However: The answer to your question would be: dim iCol = Range("whichcolumn") Range(activecell.row,icol) = or ActiveCell.EntireRow.Cells(1,iCol) = keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool rbekka33 wrote : I have a userform which allows people to select a column and places that value in cell iv65536. I have then named this cell as WhichColumn. The column as a reference changes regularly and I used to have to go into the code to change the offset value. It is part of an IF statement Old code Else 'if the charge exists ActiveCell.Offset(0, 23).Range("A1").Select ActiveCell.Range("A1,F1").Select Selection.Copy Worksheets("Sheet2").Select c.Offset(0, 1).Select ActiveSheet.Paste Worksheets("Sheet1").Select ActiveCell.Offset(1, -23).Range("A1").Select vSourceCharge = ActiveCell.Value End If What i want to do is set the column reference to refer to the value in cell iv65536 which has the value eg Sheet1!$S:$S. Does anyone know how to refer to a named range or get the contents of that cell and use in a relative reference? thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Naming Cells Using Relative Cell References | Excel Worksheet Functions | |||
Help with converting a block of cells with Absolute and mixed references to relative references | Excel Worksheet Functions | |||
Relative, absolute & mixed references in many cells | Excel Worksheet Functions | |||
Named formulas in CHOOSE need to be Relative references when paste | Excel Worksheet Functions | |||
Confused about relative references in named formulas | Excel Worksheet Functions |