Excel Vba how to insert formula with code

[Javi]

I have a range of cells I would like to insert the below formula into. The range is “G5:G10000” keeping in mind the formula needs to change for each row as it would if you copied and pasted it down a column.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
=IF(AND(E5>=$C$1,E5<=$D$1-1),$C$2,IF(AND(E5>=$D$1,E5<=$E$1-1),$D$2,IF(AND(E5>=$E$1,E5<=$F$1-1),$E$2,IF(AND(E5>=$F$1,E5<=$G$1-1),$F$2,IF(AND(E5>=$G$1,E5<=$H$1-1),$G$2,IF(AND(E5>=$H$1,E5<=$I$1-1),$H$2,IF(AND(E5>=$I$1,E5<=$J$1-1),$I$2,IF(AND(E5>=$J$1,E5<=$K$1-1),$J$2,0))))))))

Thanks for the assistance

 

[VoG]

Try

Range("G5:G10000").Formula = "=IF(AND(E5>=$C$1,E5<=$D$1-1),$C$2,IF(AND(E5>=$D$1,E5<=$E$1-1),$D$2,IF(AND(E5>=$E$1,E5<=$F$1-1),$E$2,IF(AND(E5>=$F$1,E5<=$G$1-1),$F$2,IF(AND(E5>=$G$1,E5<=$H$1-1),$G$2,IF(AND(E5>=$H$1,E5<=$I$1-1),$H$2,IF(AND(E5>=$I$1,E5<=$J$1-1),$I$2,IF(AND(E5>=$J$1,E5<=$K$1-1),$J$2,0))))))))"

 

[shippey121]

one way to do this without code is select cell G5, then in the name range box where it should say G5 click the box and type G5:G10000 and press enter, this will now select the range, now type in the formula but dont just press enter, press CTRL + ENTER, this will put the formula in all the cells selected

if you already have the formula in G5 after typing the range in the named range box press F2 to edit the formula the CTRL ENTER to insert the formula to the rest

HTH

 

[Javi]

Thanks All!!

I used this code and it worked well.

Dim my_range As Range

With Worksheets("Analyzer - Calculator")

Set my_range = .Range("G5:G10000")
my_range.Formula = _
"MyFormulawenthere"

How do you put the code in a separate window on this forum? YES I'm new to this!


End With

 

[VoG]

To add code tags

[code]
your code here
[/code]

 

[circledchicken]

Hi Javi,

In case it's any use to you, I think you can shorten your formula to achieve the same result - try the following array formula (entered with CTRL-SHIFT-ENTER):

=IF(SUM((E5>=$C$1:$J$1)*(E5<=$D$1:$K$1-1))=0,0,INDEX(($C$2:$J$2),MATCH(1,(E5>=$C$1:$J$1)*(E5<=$D$1:$K$1-1),0)))

The first part checks for no match in which case a 0 is returned. The next part finds and returns the first category where both conditions are true.

or if you have Excel 2007+ then you can simplify the error checking with the following (also entered with CTRL-SHIFT-ENTER):

=IFERROR(INDEX(($C$2:$J$2),MATCH(1,(E5>=$C$1:$J$1)*(E5<=$D$1:$K$1-1),0)),0)

 

[Javi]

Thanks Got it!