Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
naming cells
How do you name a cell so you can use it in a formula?
I want, say, a,10 to be "Social" or something. Is this possible? thanks john |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
naming cells
Basic method of naming a range:
1) Click on A10 2) Click in the name box (just to the left of the formula bar above A1) 3) type Social Edit or delete the named range: You can edit your named range using Insert, Name, Define Use a name to hold a constant If you have a constant value, you can: 1) Insert, Name Define to create a name 2) Place the value of the constant in in the 'refers to' area. Example: if the value of 'Social' is always '3', create the name 'Social' then type '=3' over the top of whatever range is current shown in the 'refers to' area. This way, your user can't accidentally overwrite the value. To refer to the value in a cell simply write '= Social'. "John" wrote in message ... How do you name a cell so you can use it in a formula? I want, say, a,10 to be "Social" or something. Is this possible? thanks john |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
naming cells
Thanks, I forgot to mention I am in vb in excel.
As I page through worksheets, the social security number is always in cell a,3. It would be cool if I could just refer to SS or Social, or something like that rather than cells(a,3). John Steve wrote: Basic method of naming a range: 1) Click on A10 2) Click in the name box (just to the left of the formula bar above A1) 3) type Social Edit or delete the named range: You can edit your named range using Insert, Name, Define Use a name to hold a constant If you have a constant value, you can: 1) Insert, Name Define to create a name 2) Place the value of the constant in in the 'refers to' area. Example: if the value of 'Social' is always '3', create the name 'Social' then type '=3' over the top of whatever range is current shown in the 'refers to' area. This way, your user can't accidentally overwrite the value. To refer to the value in a cell simply write '= Social'. "John" wrote in message ... How do you name a cell so you can use it in a formula? I want, say, a,10 to be "Social" or something. Is this possible? thanks john |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
naming cells
Try this John - assuming your cell is a range named "SS" -
Sub SSAN() Dim Social As String Social = Range("SS").Value MsgBox Social End Sub Gary |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
naming cells
Try this John - assuming your cell is a range named "SS" -
Sub SSAN() Dim Social As String Social = Range("SS").Value MsgBox Social End Sub Gary |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
naming cells
John
Just assign it to a variable in your code. This should give you an idea Sub test() For Each sh In Worksheets ssNum = sh.Range("A3") 'your code goes here 'Anywhere you want to reference the ss number ' in your code juse us ssNum per the msgbox example below MsgBox "The ss number in sheet " & sh.Name & " is " & ssNum ' for your test purpose. Next sh End Sub "John" wrote in message ... Thanks, I forgot to mention I am in vb in excel. As I page through worksheets, the social security number is always in cell a,3. It would be cool if I could just refer to SS or Social, or something like that rather than cells(a,3). John Steve wrote: Basic method of naming a range: 1) Click on A10 2) Click in the name box (just to the left of the formula bar above A1) 3) type Social Edit or delete the named range: You can edit your named range using Insert, Name, Define Use a name to hold a constant If you have a constant value, you can: 1) Insert, Name Define to create a name 2) Place the value of the constant in in the 'refers to' area. Example: if the value of 'Social' is always '3', create the name 'Social' then type '=3' over the top of whatever range is current shown in the 'refers to' area. This way, your user can't accidentally overwrite the value. To refer to the value in a cell simply write '= Social'. "John" wrote in message ... How do you name a cell so you can use it in a formula? I want, say, a,10 to be "Social" or something. Is this possible? thanks john |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
naming cells
I am a beginner so I'm still confused about this. I want to create a
variable (I think) called SS# which stands for worksheets(4).cells(2,3) or whatever. Anytime I use that variable in vb code in excel, vb & excel treats it like cells(2,3). So I can code things like ss# = worksheets(n).cells(3,4) and cells(2,3) will change accordingly. Also the opposite: Cells(9,11) = ss# will put the current value of cells(2,3) into cells(9,10) It look like, in your example below, I can do this with: ssNum = Range("B3") and then use ssNum instead of cells(a3). I haven't tried it yet. This isn't a huge deal. It just saves typing alot and makes the code a lot more readable. Thanks John Steve wrote: John Just assign it to a variable in your code. This should give you an idea Sub test() For Each sh In Worksheets ssNum = sh.Range("A3") 'your code goes here 'Anywhere you want to reference the ss number ' in your code juse us ssNum per the msgbox example below MsgBox "The ss number in sheet " & sh.Name & " is " & ssNum ' for your test purpose. Next sh End Sub "John" wrote in message ... Thanks, I forgot to mention I am in vb in excel. As I page through worksheets, the social security number is always in cell a,3. It would be cool if I could just refer to SS or Social, or something like that rather than cells(a,3). John Steve wrote: Basic method of naming a range: 1) Click on A10 2) Click in the name box (just to the left of the formula bar above A1) 3) type Social Edit or delete the named range: You can edit your named range using Insert, Name, Define Use a name to hold a constant If you have a constant value, you can: 1) Insert, Name Define to create a name 2) Place the value of the constant in in the 'refers to' area. Example: if the value of 'Social' is always '3', create the name 'Social' then type '=3' over the top of whatever range is current shown in the 'refers to' area. This way, your user can't accidentally overwrite the value. To refer to the value in a cell simply write '= Social'. "John" wrote in message ... How do you name a cell so you can use it in a formula? I want, say, a,10 to be "Social" or something. Is this possible? thanks john |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
naming cells
John
I was working on the supposition that each sheet in your workbook would contain you ssNum. Therefore, to reference it, you would need to loop through your worksheets and read it each time you change sheet. My code below will help you understand what is happening if you copy and paste it into a new module and run it. You will need to change the range address to the address where you store your ss number. A range address is exactly the same as you see it in a spreadsheet (cell reference B3 in your sheets in referenced using Range("B3") using VBA). If your ss number is contstant, you can reference it once wherever it is stored. using either cells() or range(). I find range()easier to read but cells() easier to work with when looping through rows and columns within a sheet. I also find it easier to understand a reference to a sheet by sheet name rather than number. Example ssNum = Worksheets("Sheet4").Range("C2") stores exactly the same value as ssNum = Worksheets("Sheet4").Cells(2,3) In many cases, this will be exaclty the same as ssNum = Worksheets(4).Cells(2,3) If you remain confused, contact me off list. HTH Steve "John" wrote in message ... I am a beginner so I'm still confused about this. I want to create a variable (I think) called SS# which stands for worksheets(4).cells(2,3) or whatever. Anytime I use that variable in vb code in excel, vb & excel treats it like cells(2,3). So I can code things like ss# = worksheets(n).cells(3,4) and cells(2,3) will change accordingly. Also the opposite: Cells(9,11) = ss# will put the current value of cells(2,3) into cells(9,10) It look like, in your example below, I can do this with: ssNum = Range("B3") and then use ssNum instead of cells(a3). I haven't tried it yet. This isn't a huge deal. It just saves typing alot and makes the code a lot more readable. Thanks John Steve wrote: John Just assign it to a variable in your code. This should give you an idea Sub test() For Each sh In Worksheets ssNum = sh.Range("A3") 'your code goes here 'Anywhere you want to reference the ss number ' in your code juse us ssNum per the msgbox example below MsgBox "The ss number in sheet " & sh.Name & " is " & ssNum ' for your test purpose. Next sh End Sub "John" wrote in message ... Thanks, I forgot to mention I am in vb in excel. As I page through worksheets, the social security number is always in cell a,3. It would be cool if I could just refer to SS or Social, or something like that rather than cells(a,3). John Steve wrote: Basic method of naming a range: 1) Click on A10 2) Click in the name box (just to the left of the formula bar above A1) 3) type Social Edit or delete the named range: You can edit your named range using Insert, Name, Define Use a name to hold a constant If you have a constant value, you can: 1) Insert, Name Define to create a name 2) Place the value of the constant in in the 'refers to' area. Example: if the value of 'Social' is always '3', create the name 'Social' then type '=3' over the top of whatever range is current shown in the 'refers to' area. This way, your user can't accidentally overwrite the value. To refer to the value in a cell simply write '= Social'. "John" wrote in message ... How do you name a cell so you can use it in a formula? I want, say, a,10 to be "Social" or something. Is this possible? thanks john |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
naming cells | Excel Worksheet Functions | |||
Range naming cells with blank cells through coding | Excel Discussion (Misc queries) | |||
naming cells | Excel Discussion (Misc queries) | |||
Naming cells | Excel Discussion (Misc queries) | |||
Naming Cells | Excel Programming |