PDA

View Full Version : enter formulain cell through VBA



ShaunC
02-15-2012, 04:10 AM
Hi All

I am trying to enter the formula:

=(L5-MIN(K5:K18))/(MAX(J5:J18)-MIN(K5:K18))

into cell S5 using VBA. The C|VBA code that I am using the fills the formula down the column. The issue I am running into is that VBA does not support the MIN or MAX functions, and I need it to be dynamic in that if I fill the formula down the column the cell references change accordingly.

I have been using Activecell.FormulaR1C1 = for all my other formula which has worked extremely well, but I cannot get it to work when utilising Application.WorksheetFunction.Min.

Would any one have a suggestion as to what I could do to get it to work?

Thank you in advance

Shaun

Bob Phillips
02-15-2012, 06:04 AM
Range("M5:M100").Formula = "=(L5-MIN(K5:K18))/(MAX(J5:J18)-MIN(K5:K18))"

ShaunC
02-15-2012, 02:48 PM
Hi xld

The code you have written worked a treat. I had tried so many combinations but couldn't get it to work.

Thank you.

Shaun