Forum Discussion

Greg Bonaparte's avatar
Greg Bonaparte
Iron Contributor
Feb 17, 2019

CORRECTION

IF S30 > 0 & IF R30 > 0 THEN U30 = S30
IF S31 > 0 & IF R31 > 0 THEN U31 = S31 ... and so on TO S629

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 17, 2019

    Hi Greg,

     

    Do you need a macro or just a formula which returns some values to U30:U629. If macro I guess it keeps previous value in the range as it was and change if condition is met. If formula what shall be returned if condition isn't met?

    Please clarify.

    • Greg Bonaparte's avatar
      Greg Bonaparte
      Iron Contributor
      Feb 17, 2019

      Hello again Sergei

       

      I'm not sure I know how to answer your question due to being very new in "macro speak". I can tell you that whenever a value is entered into the U30 thru U629 column, the value in T30 thru T629 decreases. Eventually the S30 column will remain zero. Not sure if this helps but here are the formula and current macro of the referenced columns:

      "S30"       =ROUNDDOWN(($Y$17-$Y$18)/$Q30,0)

      "T30"       =$Y$17-$Y$18

      "U30"       EMPTY CELL (currently waiting for manual input to trigger the macro below)

       

      Private Sub Worksheet_Change(ByVal Target As Range)

      ActiveSheet.Range("$AW$21:$AX$600").AutoFilter Field:=1

      If Not (Application.Intersect(Range(" U30:U600"), Target) Is Nothing) Then



      If MsgBox("Select 'No' until ADDITIONAL SHARES are manually entered. Have you finished manually entering 'ADDITIONAL SHARES' ?", vbQuestion + vbYesNo, "") = vbYes Then

      Call ManualCalculate


      End If

      End If


      End Sub

       

Resources