Naming a range in a module
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 |
Naming a range in a module
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 |
Naming a range in a module
On Jan 10, 9:52*am, Sue wrote:
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 |
Naming a range in a module
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 On Thu, 10 Jan 2008 01:52:01 -0800, Sue wrote: 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 |
Naming a range in a module
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 &)" " wrote: On Jan 10, 9:52 am, Sue wrote: 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 |
Naming a range in a module
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 "Incidental" wrote: 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 |
All times are GMT +1. The time now is 04:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com