Simple Macro Question

RWinfield · Apr 21st, 2002, 02:35 PM

I have a worksheet with a column D "Quantity" and a column E "Level", both of which are named ranges. The formula I am using in column F is quite simple =IF(quantity
This works on the worksheet, but I don't want it to recalculate every time a value is changed.

What I have been trying to do (unsucessfully) for the last hour or two is to incorporate this formula into a macro that can be called from a button on a userform. I would like to remove the formulas from column F and just have the procedure run when the button on the userform is clicked. Ideally, I would like to be able to select the last cell with information in it from column D and use that as the end of the loop. This seems to be simple but I just can't figure out how to do this.
Any help would be appreciated.

[ This Message was edited by: RWinfield on 2002-04-21 13:37 ]

[ This Message was edited by: RWinfield on 2002-04-21 13:37 ]

[ This Message was edited by: RWinfield on 2002-04-21 13:38 ]

RWinfield · Apr 21st, 2002, 02:39 PM

the fromula is =IF(quantity

RWinfield · Apr 21st, 2002, 02:41 PM

well for some reason I cannot seem to get the entire formula into the message. Obviously not my day.
Lets try this: if quantity < level "X" else ""

Aladin Akyurek · Apr 21st, 2002, 02:44 PM

Check

Disable HTML on this Post

in particular when you post a formula with a less than operator in it.

RWinfield · Apr 21st, 2002, 02:47 PM

Thanks Aladin I will remeber that one

Tom Urtis · Apr 21st, 2002, 03:08 PM

Try a macro like this:

Sub FormYouLah()
'Insert formula in column F, dynamic range dependent on column E
Range("F1", Range("E65536").End(xlUp).Offset(, 1)).Formula = "=IF(RC[-2] 'Replace the formulas in F with values, so they don't recalculate:
Range("F1", Range("F65536").End(xlUp)).Value = Range("F1", Range("F65536").End(xlUp)).Value
End Sub

Modify the No condition result for whatever you meant by "else" in your question.

RWinfield · Apr 21st, 2002, 06:14 PM

Thanks Tom, that was what I needed. The only modification I made was to replace "NO" with "" as in the original question, to enter (or not enter depending on how you look at it) a null value to the other cells. The purpose in that was to expand the macro to select all rows with an "X" in them and copy them to another sheet. Thanks again for your help

Rick

Apr 21st, 2002, 03:08 PM	#6
Tom Urtis MrExcel MVP Join Date: Feb 2002 Location: San Francisco, California USA Posts: 10,387	Try a macro like this: Sub FormYouLah() 'Insert formula in column F, dynamic range dependent on column E Range("F1", Range("E65536").End(xlUp).Offset(, 1)).Formula = "=IF(RC[-2] 'Replace the formulas in F with values, so they don't recalculate: Range("F1", Range("F65536").End(xlUp)).Value = Range("F1", Range("F65536").End(xlUp)).Value End Sub Modify the No condition result for whatever you meant by "else" in your question. __________________ Tom Urtis Microsoft MVP - Excel

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 21st, 2002, 02:35 PM	#1
RWinfield New Member Join Date: Feb 2002 Location: Washington State Posts: 33	I have a worksheet with a column D "Quantity" and a column E "Level", both of which are named ranges. The formula I am using in column F is quite simple =IF(quantity This works on the worksheet, but I don't want it to recalculate every time a value is changed. What I have been trying to do (unsucessfully) for the last hour or two is to incorporate this formula into a macro that can be called from a button on a userform. I would like to remove the formulas from column F and just have the procedure run when the button on the userform is clicked. Ideally, I would like to be able to select the last cell with information in it from column D and use that as the end of the loop. This seems to be simple but I just can't figure out how to do this. Any help would be appreciated. [ This Message was edited by: RWinfield on 2002-04-21 13:37 ] [ This Message was edited by: RWinfield on 2002-04-21 13:37 ] [ This Message was edited by: RWinfield on 2002-04-21 13:38 ]

Apr 21st, 2002, 02:39 PM	#2
RWinfield New Member Join Date: Feb 2002 Location: Washington State Posts: 33	the fromula is =IF(quantity

Apr 21st, 2002, 02:41 PM	#3
RWinfield New Member Join Date: Feb 2002 Location: Washington State Posts: 33	well for some reason I cannot seem to get the entire formula into the message. Obviously not my day. Lets try this: if quantity < level "X" else ""

Apr 21st, 2002, 02:44 PM	#4
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	Check Disable HTML on this Post in particular when you post a formula with a less than operator in it.

Apr 21st, 2002, 02:47 PM	#5
RWinfield New Member Join Date: Feb 2002 Location: Washington State Posts: 33	Thanks Aladin I will remeber that one

Apr 21st, 2002, 06:14 PM	#7
RWinfield New Member Join Date: Feb 2002 Location: Washington State Posts: 33	Thanks Tom, that was what I needed. The only modification I made was to replace "NO" with "" as in the original question, to enter (or not enter depending on how you look at it) a null value to the other cells. The purpose in that was to expand the macro to select all rows with an "X" in them and copy them to another sheet. Thanks again for your help Rick