defining range to be used in formula

alpinescott

New Member
Joined
Sep 24, 2004
Messages
48
Hello,

I'm trying to get a macro to select a range, name it, and then insert that range (using the new name) into a formula, to be entered in another cell.
I thought this would be pretty straightforward, and think that I'm missing something trivial, but I'm stumped.
Here's a chunk of the code:

Dim rng5 As Range
ActiveCell.Offset(-1, d).Resize(3, 1).Select
Set rng5 = Selection
Range("E382").Select
ActiveCell.Offset(0, c).Select
ActiveCell = "=AVERAGE(rng5)"

This returns the #NAME? error in all cells that the formula is entered in.
I think it's something to do with how I Dim or Set rng5, but I've tried several things, and no dice. Or it needs to be entered differently in the formula?
If anyone has any ideas, they'd be appreciated.

Thanks, Scott
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hi, Scott,
declaring a variable in VBA is not the same as defining a name
when you ask your best friend - the macro recorder - you get this

Code:
ActiveWorkbook.Names.Add Name:="rng5", RefersToR1C1:="=sheet1!R1C1"
(cell A1 on sheet1)
if you can explain a bit more how the address is defined for the named range, we can help further

kind regards,
Erik

EDIT: sorry changed wrong paste of code
 
Upvote 0
Erik,

The range is nested within a loop, so the cell references will change with each run through the loop (one run per year). The macro uses the value of another cell (julian day # 1-366), to find that day in a static range (D2:D367) in this case, and then offset to the value for that day in a given year. I'm trying to get the macro to return an average of a 3 cell range including the value found by the macro, in the previous sentence. Here's the entire code, I apologize that it's not in the HTML format, for whatever reason I don't have the Add-In Manager in my VBE, and I don't have the proper disc to install it.

'finds julian day for Hm, (3 day avg), inserts Q value in table for that date
For i = 1 To a
Range("E381").Select
ActiveCell.Offset(0, c).Select
If ActiveCell = "" Then
c = c + 2
d = d + 2
Else
Set rng4 = ActiveCell
rn4 = rng4.Value
Range("D2:D367").Select
Selection.Find rn4
Selection.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(-1, d).Resize(3, 1).Select
Set rng5 = Selection
Range("E382").Select
ActiveCell.Offset(0, c).Select
ActiveCell = "=AVERAGE(rng5)"
c = c + 2
d = d + 2
End If
Next i

Cheers, Scott
 
Upvote 0
Scott,

so you don't have to define a name, but you want to include an address in your formula ?

try something like
ActiveCell = "=AVERAGE(" & Selection.Address & ")"

REMARQUE
you are activating and selecting cells which makes the macro rather difficult to read
I think this might do the job
Code:
Range("E382").Offset(0, c).Formula = "=AVERAGE(" & Selection.Address & ")"

to paste code just use the CODE-button on your message-page

best regards,
Erik
 
Upvote 0
Erik,

Thank you, that worked perfectly! Still teaching myself, every reply helps me clean things up a bit more. Much appreciated.

Cheers, s
 
Upvote 0
Yeah, Scott
cheers (y)
often the problem is the way we ask our questions and reply the answers
a real-life-example will often help

When you start your question with
"the result I would like to get is something like:
A1: =AVERAGE($A$2:$A$5)"

then nobody will think here
... to select a range, name it, and ... (using the new name)
that you really need a named range

... always trying to find out how others look to the things :) ...

God bless you!
Erik
 
Upvote 0

Forum statistics

Threads
1,207,089
Messages
6,076,518
Members
446,211
Latest member
b306750

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top