How to refer a named cell in Excel with VBA? 3

[gzhong]

I am trying to avoid using Sheet.cells(col,row) by defining names to those cells I am going to use later on. Is there a way to use the value of named cells by calling their names in VBA?

Thanks,

Gregory

 

[Databaseguy]

Here's a couple of ideas

Application.Goto Reference:="YourRange"

Set MyRange = objexcel.Cells(x + 1, 2) Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[SHardy]

If you are simply want to use the VALUE of the cell, then use the following:

Range("RangeName&quot.Value

Examples:

1) To set the value to a variable
varname = Range("RangeName&quot.Value

2) To use the value in a calculation
newval = Range("RangeName&quot.Value * 1.175

3) To incement the cell's value
Range("RangeName&quot.Value = Range("RangeName&quot.Value + 1

Hope this helps.

 

[SHardy]

To "increment"...

 

[dsi]

If you are just naming cells, it is much easier than that. Let's say you name cell B13 as 'ProductID'. The usage depends on how the cell is formatted. To use this value in VBA:

Dim sVal As String
Dim dVal As Double
Dim iVal As Integer

'echo original value
MsgBox [ProductID]

'store value (formatted as text)
sVal = [ProductID]

'store value (formatted as number)
dVal = CDbl([ProductID])

'store value (formatted as a whole number)
iVal = CInt([ProductID])

 

[SHardy]

Well you learn something new everyday.

 

[gzhong]

Thanks all for your help. For my application, DSI's code works perfect.

Gregory