named ranges, I think

I

inquirer

Can I use named ranges to make my code easier to follow?
What I would like to do is something like this:

rnage("a1").select
lastrow=Range(Selection, Selection.End(xlDown)).Cells.Count
name cells A2 to Alastrow as price
name cells B2 to Blastrow as quantity
name cells C2 to Clastrow as cost

for i=2 to lastrow
cost(i)=price(i)*quantity(i)
next i
This is more meaningful than
cells(i,"c").value=cells(i,"a").value*cells(i,"b").value

Or is there a better and clearer way to do this?
Thanks
Chris
 
B

Bob Kilmer

inquirer said:
Can I use named ranges to make my code easier to follow?
What I would like to do is something like this:

rnage("a1").select
lastrow=Range(Selection, Selection.End(xlDown)).Cells.Count
name cells A2 to Alastrow as price
name cells B2 to Blastrow as quantity
name cells C2 to Clastrow as cost

for i=2 to lastrow
cost(i)=price(i)*quantity(i)
next i
This is more meaningful than
cells(i,"c").value=cells(i,"a").value*cells(i,"b").value

Or is there a better and clearer way to do this?
Thanks
Chris
Click to expand...

Some options...

Option Explicit

'----------------------------------------
Sub main()
Dim cost As Range
Dim quantity As Range
Dim price As Range

Set price = Range("A1:A10").Cells
Set quantity = Range("B1:B10").Cells
Set cost = Range("C1:C10").Cells

Dim i As Integer
For i = 1 To 10
cost(i).Value = _
price(i).Value * _
quantity(i).Value
Next i
End Sub

'----------------------------------------
Sub main2()
'If ranges are named...
'(I had trouble naming ranges in code.
'Don't know what the trouble is.
'I've done it before. Not working tonight.
'Tired? Not thinking clearly?)
Dim i As Long
For i = 1 To 10
Range("cost")(i).Value = _
Range("price")(i).Value * _
Range("quantity")(1).Value
Next i
End Sub

'----------------------------------------
Sub main3()
Const price = "a"
Const quantity = "b"
Const cost = "c"
Dim i As Long
For i = 1 To 10
Cells(i, cost).Value = _
Cells(i, price).Value * _
Cells(i, quantity).Value 'or

Columns(cost).Rows(i).Value = _
Columns(price).Rows(i).Value * _
Columns(quantity).Rows(1).Value 'or

Columns(cost).Cells(i).Value = _
Columns(price).Cells(i).Value * _
Columns(quantity).Cells(i).Value
Next i
End Sub
 
I

inquirer

Mnay thanks Bob, I will try all the otpions bit I think the first one is the
one I will use.
Chris
 
Top