Help needed - using variable ranges in formulas via macro

[EllieCat]

Hi There,

I have a macro which is going down a list of data, finding where the relavant sections start and end, and then I need to feed these back into a formula elsewhere.

What I've got (which doesn't work!) is:

mycell.Formula = "=mode(" & Range(rangestart, rangeend) & ")"

Can anyone spot what I'm doing wrong? Or does anyone know an alternative way to find out the mode for values matching specific criteria?

Thanks!

Ellie-Cat

 

[GlennUK]

Shouldn't that be:

mycell.Formula = "=mode(" & Range(rangestart, rangeend).Address & ")"

?

 

[EllieCat]

Yes, it should be!

Only problem is it's giving me ranges without the sheet info in (i.e. my data and summary tables are on different sheets, and it's giving me the right range on the summary sheet instead of the data sheet)

I can work around by moving them on to a single sheet, but does anyone know how to get it to work across sheets correctly?

Thanks for your help Glenn

EllieCat

 

[GlennUK]

In that case, put the sheet name in the formula:

mycell.Formula = "=mode(Sheet1!" & Range(rangestart, rangeend).Address & ")"

or

mycell.Formula = "=mode('Another sheet'!" & Range(rangestart, rangeend).Address & ")"

 

[mikerickson]

Alternatly, your formula could refer to a named range.

myCell.Formual = "=MODE(myNamedRange)"

And your updating code would define that name, rather than changing a formula

Range(rangeStart, rangeStop).Name = "myNamedRange"

 

[GlennUK]

Alternatly, your formula could refer to a named range.

myCell.Formual = "=MODE(myNamedRange)"

And your updating code would define that name, rather than changing a formula

Range(rangeStart, rangeStop).Name = "myNamedRange"

many ways to skin that cat