• 411K Members
• 5,855 Online
• 466K Conversations

Contributor

# Create a macro saying...

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

Can someone help?

12 Replies

# Re: Create a macro saying...

CORRECTION

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

# Re: Create a macro saying...

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?

# Re: Create a macro saying...

Only make the first formula, and use copy and paste in correct direction in a select of 600 cells,

Its more simple

# Re: Create a macro saying...

That could be

`=IF((S30 > 0)*(R30 > 0), S30 ,"")`

in U30, and drag it down

# Re: Create a macro saying...

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

# Re: Create a macro saying...

`=IF((S30 > 0)*(R30 > 0), S30 ,"")`

While this worked, It deleted the formulas of column U30, So I think maybe a macro is required?

This also has to run after many other macros and formulas have run. So It has to be triggered from a macro I think.

# Re: Create a macro saying...

Nope, that doesn't work, base on your latest post you need a macro, not formula

# Re: Create a macro saying...

I have opened several conversations, each worded differently, but no one so far has been able to assist. I'm hoping that the language I'm using is not the reason no one has helped. Thank you however for your efforts.

# Re: Create a macro saying...

Excel change automatic address cell in the correct way vertical and horizontal in multiple paste

# Re: Create a macro saying...

Not use \$ because is for static cell address

# Re: Create a macro saying...

Yes, I think I did what you describe here. I put =IF((\$S30 > 0)*(\$R30 > 0), \$U30=\$S30,"") in an empty column and pasted it down. I believe it failed because it was functioning prematurely (before the full algorithm required its results). I need this to run near the end of a long process. So it needs to be a "CALL" from a macro I think.

# Re: Create a macro saying...

In addition, each time the IF function enters a value into U30, the new U30 value may modify the value in U31 and so on. Therefore the function must execute, then wait for U31 change, before it increments to next line down.

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies