CODEWITHSUNDEEP
vbaexcelformula
Ravi Kant Agarwal
Ravi Kant Agarwal

Reputation: 35

How to put a VBA formula using dynamic variables capturing Address?

I am running a Loop to capture the start of Range and end of Range for a particular scenario. After I find my start range and end range, I want to put it as a formula in a cell. For example:

Cells(1,1).Formula  "= Min( startrange:endrange)"

The above code has assumed the variables as text and instead of putting the cell address these variables are handling, it is pasting the formula as text like this : '= Min( startrange:endrange)'

I have no idea and have tried different approaches I could get from internet like below 

'    cells(4,4).formula = "=Min("&startrage& :"" & endrange & ")""
'      Cells(4, 4).Formula = "=Min(startrange.value :endrange)"

'Cells(4, 4).Formula = Application.WorksheetFunction.Min(startrange:endrange)
 '   Cells(4, 4).Formula =  "=Min("&startrange&"&":"& " &endrange&")"

where 

 startrange = ActiveCell.Offset(1, 3).Address(0, 0)
endrange = ActiveCell.Offset(0, 3).Address(0, 0)

Nothing works.

How can I achieve this? Specially I am also facing error in handling ":".

Upvotes: 0

Views: 298

Answers (1)

Scott Craner
Scott Craner

Reputation: 152485

cells(4,4).formula = "=Min(" & startrage & ":" & endrange & ")"

Upvotes: 1

Related Questions