Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Simple Macro Question

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Washington State
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    New Member
    Join Date
    Feb 2002
    Location
    Washington State
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    the fromula is =IF(quantity

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    Washington State
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ""

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,060
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    Check

    Disable HTML on this Post

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


  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    Washington State
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Aladin I will remeber that one

  6. #6
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  7. #7
    New Member
    Join Date
    Feb 2002
    Location
    Washington State
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •