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

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