Naming a range in a module

[Sue]

I want to select a two cell range and name it using Excel Basic.

The code below does select the two cells with the coordinates of the lower
cell being (ReqRow,ReqCol).

The first two lines of the naming code compile and run but produce nothing
ie no name in list of range names. AccCode is correctly read previously
however. It is a 3 letter string eg veh.

The third line of the naming code will not compile - how do you name a range
rather than a single cell?

Help! There is so much I don't know!

Sue

ReqRow = ActiveCell.Row
ReqCol = ActiveCell.Column
ActiveCell.Offset(-1, 0).Range("A1:A2").Select
ActiveWorkbook.Names.Add Name:="ac" & AccCode, RefersToR1C1:= _
"'Work Data'!R[" & ReqRow & "]C[" & ReqCol & "]:" _
'Comment [" & ReqRow & - 1"]C[" & ReqCol & "]""
ActiveCell.Offset(1, 2).Range("A1").Select

 

[Incidental]

Hi Sue

From your code it looks like you have recorded a macro and you are
trying to incorporate it into your code? depending on why you want to
name the range you could use a different method which is to set the
range using VBA.

This code is an example of how setting a range to a variable that you
can refer to in your code. Just copy it into a module and step
through it (pressing the F8 key to run the code line by line) and it
should give you an idea of how it works.

Option Explicit
Dim MyRng As Range 'Declare your range

Sub SetARange()

Set MyRng = Range(ActiveCell, ActiveCell.Offset(1, 0))

[MyRng].Select

[MyRng].Value = "I'm a range called MyRng"

[MyRng].Copy Destination:=[A1]

End Sub

I hope this is of some use to you

Steve

 

[paul.robinson]

I want to select a two cell range and name it using Excel Basic.

The code below does select the two cells with the coordinates of the lower
cell being (ReqRow,ReqCol).

The first two lines of the naming code compile and run but produce nothing
ie no name in list of range names. AccCode is correctly read previously
however. It is a 3 letter string eg veh.

The third line of the naming code will not compile - how do you name a range
rather than a single cell?

Help! There is so much I don't know!

Sue

            ReqRow = ActiveCell.Row
            ReqCol = ActiveCell.Column
            ActiveCell.Offset(-1, 0).Range("A1:A2").Select
            ActiveWorkbook.Names.Add Name:="ac" & AccCode, RefersToR1C1:= _
                 "'Work Data'!R[" & ReqRow & "]C[" & ReqCol & "]:" _
'Comment                 [" & ReqRow & - 1"]C[" & ReqCol &"]""
            ActiveCell.Offset(1, 2).Range("A1").Select

Hi
Naming Ranges is much easier than naming formulas or strings. Simply
do

ActiveCell.Offset(-1, 0).Range("A1:A2").Name = "ac" & AccCode

This name will apply to the active sheet. If you only want it to apply
to a specific sheet like Sheet1 use

ActiveCell.Offset(-1, 0).Range("A1:A2").Name = "Sheet1!ac" & AccCode

regards
Paul

 

[Gord Dibben]

Sue

ReqRow = ActiveCell.Row
ReqCol = ActiveCell.Column
ActiveCell.Offset(-1, 0).Range("A1:A2").Select
ActiveWorkbook.Names.Add Name:="ac" & AccCode, RefersToR1C1:= _
"='Work Data'!R" & ReqRow & "C" & ReqCol & ":R" _
& ReqRow - 1 & "C" & ReqCol & ""
ActiveCell.Offset(1, 2).Range("A1").Select

I assume AccCode is a constant or variable from soemwhere.


Gord Dibben MS Excel MVP

 

[Sue]

Hi Paul. You are wonderful! There is now a Range Name called acveh in the
Range Name list!
However I now want that put in the DSUM formula. If I type acveh it produces
the correct answer, so it is what I want. However I have to get it there from
the value of the variable CurCode (which now is the range name of the
criterion). Can you help with the syntax? My attempts have either produced
#Name? or the Yellow Bar on the last line.

I realise that my main problem at present is feeding data back from
varaibles. I can assign data to variables but I find getting it put where I
want it not easy!

AccCode has been acquired from a form correctly eg veh
ReqRow = ActiveCell.Row
ReqCol = ActiveCell.Column
CurCode = "ac" & AccCode
ActiveCell.Offset(-1, 0).Range("A1:A2").Name = CurCode
Comment - there is now a two cell range called acveh in the Range list which
is the range of the criterion I want in the formula below.
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=DSUM(CPData,7,& CurCode &)"

I want to select a two cell range and name it using Excel Basic.

The code below does select the two cells with the coordinates of the lower
cell being (ReqRow,ReqCol).

The first two lines of the naming code compile and run but produce nothing
ie no name in list of range names. AccCode is correctly read previously
however. It is a 3 letter string eg veh.

The third line of the naming code will not compile - how do you name a range
rather than a single cell?

Help! There is so much I don't know!

Sue

ReqRow = ActiveCell.Row
ReqCol = ActiveCell.Column
ActiveCell.Offset(-1, 0).Range("A1:A2").Select
ActiveWorkbook.Names.Add Name:="ac" & AccCode, RefersToR1C1:= _
"'Work Data'!R[" & ReqRow & "]C[" & ReqCol & "]:" _
'Comment [" & ReqRow & - 1"]C[" & ReqCol & "]""
ActiveCell.Offset(1, 2).Range("A1").Select

Hi
Naming Ranges is much easier than naming formulas or strings. Simply
do

ActiveCell.Offset(-1, 0).Range("A1:A2").Name = "ac" & AccCode

This name will apply to the active sheet. If you only want it to apply
to a specific sheet like Sheet1 use

ActiveCell.Offset(-1, 0).Range("A1:A2").Name = "Sheet1!ac" & AccCode

regards
Paul

 

[Sue]

Thank you for your time on my problem. I have learnt more from what you have
said but Paul below understood exactly what I was trying to say. Sue