naming cells

[John]

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

 

[Steve]

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]

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

 

[Gary K]

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

 

[Gary K]

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

 

[Steve]

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]

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]

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