1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

negative view of macros!

Discussion in 'Business Applications' started by anon125, Aug 11, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. anon125

    anon125 Thread Starter

    Joined:
    Aug 11, 2009
    Messages:
    21
    with excel 2002 is there any way of limiting a value to positive numbers?
    perhaps in the dim area?
    i have a value defined as range - whatever that means!
    thanks
     
  2. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    wouldn't you just use > 0 to check?
     
  3. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Is the value derived from a formula? If not, what is your macro?
    Not enough info to help much.
     
  4. anon125

    anon125 Thread Starter

    Joined:
    Aug 11, 2009
    Messages:
    21
    thnks for your help
    the RRSP should not go below zero

    Sub zerotest()
    Dim seekValue As Double
    Dim RRSP As Range
    Inflation = Range("O7").Value
    seekValue = 12345
    'seekValue = InputBox("What's the value to seek?")
    For Each RRSP In Range("N14:N32").Cells
    'Do Until RRSP < 10
    RRSP.GoalSeek Goal:=seekValue, changingCell:=RRSP.Offset(0, -4)
    'Do Until RRSP < 10
    ' Loop
    seekValue = seekValue + (seekValue * Inflation)
    ' Loop
    Next RRSP
    End Sub

    http://www.4shared.com/file/124634636/adac13e3/_2__temploop.html
    here is the file
    column J should not go below zero.
     
  5. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    can you explain the steps you take to calculate, I rarely use Goal seek. Also you have commented out several lines in the code...
     
  6. anon125

    anon125 Thread Starter

    Joined:
    Aug 11, 2009
    Messages:
    21
    i don't understand the macro either!
    the commented out parts are there to show what i tried and did not work.
    thanks
     
  7. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    A few things. GoalSeek must be a variant, but, as I understand it (from using in cells, not VBA), there really can't be a way to prevent it from finding a negative value, it will just calculate the answer. You could have a line such as (Not VBA here, but Ziggy can translate)
    IF(RRSP<0,0,RRSP)
    I suppose that would be an IF line and an Else line....
    But why do you not want to show any negative inflation (deflation)?
     
  8. anon125

    anon125 Thread Starter

    Joined:
    Aug 11, 2009
    Messages:
    21
    The column is where i can draw out (extra) money from a pension savings - but i cannot put money back in.
    thanks
     
  9. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    I just meant tell me the steps you take to do it manually...or record a macro that runs through the steps so I know what you are doing.

    if anything I can give you some code that can loop through the cells after the Goal seek is completed and delete the negatives?
     
  10. anon125

    anon125 Thread Starter

    Joined:
    Aug 11, 2009
    Messages:
    21
    quite simple really
    i alter column J until column N meets the requred value.
    seekValue = 12345
    also it would be a help if seekvalue could read a cell with the seekvalue in it.
    thanks

    ps here is a solver attempt
    Sub abovezero()
    '
    ' abovezero Macro
    ' Macro recorded 15/07/2009 by R
    '
    '
    Range("N15").Select
    SolverOk SetCell:="$N$15", MaxMinVal:=2, ValueOf:="25000", ByChange:="$J$15"
    SolverAdd CellRef:="$J$15", Relation:=3, FormulaText:="0"
    SolverOk SetCell:="$N$15", MaxMinVal:=2, ValueOf:="25000", ByChange:="$J$15"
    SolverSolve
    End Sub
     
  11. anon125

    anon125 Thread Starter

    Joined:
    Aug 11, 2009
    Messages:
    21
    just a thought, maybe if column N already exceeds seekvalue and column J is below zero then set column J to zero (on just that line of course!)
     
  12. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    here try this I adapted your solver... it will put a zero if negatives are inserted


    Code:
    Option Explicit
    
    Sub abovezero()
    '
    ' abovezero Macro
    ' Macro recorded 15/07/2009 by R
    ' mod Ziggy
    '
    
    Dim R As Integer
    Dim N As String
    Dim J As String
    
    ' I just hard coded this but if it is a variable range I can add that instead
    For R = 11 To 46
    
    
    N = "$N$" & R
    J = "$J$" & R
    
    
    'MsgBox Range(J).Value
    
    Range(N).Select
    SolverOk SetCell:=N, MaxMinVal:=2, ValueOf:="25000", ByChange:=J
    SolverAdd CellRef:=J, Relation:=3, FormulaText:="0"
    SolverOk SetCell:=N, MaxMinVal:=2, ValueOf:="25000", ByChange:=J
    
    If Range(J).Value < 0 Then
    ' changes negatives to zero
    Range(J) = 0
    
    End If
    
    'makes Reult pop up go through
    SolverSolve userFinish:=True
    
    
    
    Next R
    
    End Sub
    
    
    
     
  13. anon125

    anon125 Thread Starter

    Joined:
    Aug 11, 2009
    Messages:
    21
    SolverOk sub or function not defined
    thanks
     
  14. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    in the VB editor...make sure code is stopped... then go to tools/references and look for SOLVER, check it off
     

    Attached Files:

  15. anon125

    anon125 Thread Starter

    Joined:
    Aug 11, 2009
    Messages:
    21
    UPDATE! it now works - thanks

    But what is it actually doing???
    what value is it setting column N to?
     

    Attached Files:

  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/851525

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice