Excel Formula and Range

[jobur]

Hi

I would like to do the following in my Excel VB macro
1/ Select a range using the variable name: SelectionRange
2/ write a formula in a cell as such
ActiveCell.Formula ="max(SelectionRange)"

I want to do that because it's part of a FOR loop and the SelectionRange changes at each loop. SelectionRange looks something like that: Range(Cells(1,j),Cell(1,j+5))

Any ideas if there is a way of doing such thing?

Thanks

Regards

J.B.

 

[SkipVought]

Hi JB,

First off, I'd advise against using the Select and Activate methods to navigate within your procedure -- How Can I Make My Code Run Faster? faq707-4105

For i = 0 to Whatever
  Select Case i
    Case 0
      SomeSheet.Cells(SomeRow, SomeCol).Formula = Application.max(SelectRange1)"
    Case 1
      SomeSheet.Cells(SomeOtherRow, SomeOtherCol).Formula = Application.max(SelectRange2)"
  End Select
Next

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[jobur]

SkipVought

Thanks for your reply. The selectionRange option I wnand to use in a formula is still giving problems.

I know the maximum number of row occupied by my data MaxDataRow. I want to set up

SelectionRange=Range(A1,A&MaxDataRow)
ActiveCell.Formula= "max(SelectionRange)

Any suggestions?

 

[SkipVought]

Set SelectionRange = Range(Cells(1,1), Cells(MaxDataRow, 1))

For each r in SelectionRange 

Next

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}