VBA for IF formulas

[newshound12]

=IF(DO32="",0,IF(CE31+DO32>DO35,B32,IF(CE31+DO32<DO36,-B36,"0")))

=IF(DO41="",0,IF(CE40+DO41>DO44,B41,IF(CE40+DO41<DO44,-B45,"0")))

=IF(DO50="",0,IF(CE49+DO50>DO53,B50,IF(CE49+DO50<DO53,-B54,"0")))

These formulas start in DO32 and end in DO923.
Each are 9 rows apart.
Need a macro to run these formulas and convert them to values

 

[newshound12]

returns a negative number in the cell if the last if condition is in play.

 

[DanteAmor]

What we intend is that you paste the formula here, add before and after each sign < > a space, because if you don't add the space, this editor deletes the formula.

Something like this:


=IF(DO32="",0,IF(CE31+DO32 > DO35,B32,IF(CE31+DO32 < DO35,-B36,"0")))

 

[Michael M]

You're not reading what we are trying to otell you...
When posting formulas on this forum, unless a space is placed either side of a "<" or ">" sign.....The forum reads it as HTML code and not a formula.
A space MUST be in place for us to read the entire formula.
We undestand the need for this, you obviously don't !

 

[newshound12]

Tried this. I must be doing something wrong.

Dim r As Long
For r = 32 To 923 Step 9
    With Range("DO" & r)
        .Formula = "=If(CE" & r - 1 & "+DO" & r & " > 
DO" & r + 3 & "B" & r & ",If(CE" & r & " 
< DO" & r + 3 & "-B36, 0)))"
        .Value = .Value
    End With

 

[Michael M]

You need to do the formula in one line

Sub MM1()
Dim r As Long
For r = 32 To 923 Step 9
    With Range("DO" & r)
        .Formula = "=IF(DO" & r & "="""",0,If(CE" & r - 1 & "+DO" & r & " > DO" & r + 3 & ",B" & r & ",If(CE" & r - 1 & "+DO" & r & " < DO" & r + 3 & ",B" & r + 4 & "*-1,0)))"
        .Value = .Value
    End With
Next r
End Sub

My earlier question was do you want these formulas to happen at the same time as the other macro....If so, use.....

Sub MM1()
Dim r As Long
Application.ScreenUpdating = False
For r = 32 To 923 Step 9
    With Range("DQ" & r)
        .Formula = "=HH" & r - 1 & "+HH" & r & "+HH" & r + 3 & "+HH" & r + 4 & ""
        .Value = .Value
    End With
     With Range("DO" & r)
        .Formula = "=IF(DO" & r & "="""",0,If(CE" & r - 1 & "+DO" & r & " > DO" & r + 3 & ",B" & r & ",If(CE" & r - 1 & "+DO" & r & " < DO" & r + 3 & ",B" & r + 4 & "*-1,0)))"
        .Value = .Value
    End With
Next r
Application.ScreenUpdating = True
End Sub

 

[newshound12]

Wow! Works like a charm!! Thanks Michael M.
The first formula is for only one column.
The 2nd formula besides going to line 923, goes for 35 columns across to column DT.
"With Range("DO" & r)" S/B "With Range("DT" & r)" (my error)
It would be great to get a macro that takes care of the 3500 formulas in one shot!

 

[Michael M]

Try....UNTESTED

Sub MM1()
Dim r As Long, c As Long
For r = 32 To 923 Step 9
    With Range("DO" & r & ":DT" & r)
        .Formula = "=IF(DO" & r & "="""",0,If(CE" & r - 1 & "+DO" & r & " > DO" & r + 3 & ",B" & r & ",If(CE" & r - 1 & "+DO" & r & " < DO" & r + 3 & ",B" & r + 4 & "*-1,0)))"
        .Value = .Value
    End With
Next r
End Sub

 

[Michael M]

And if you wanted them both in the one macro.....

Sub MM1()
Dim r As Long, c As Long
For r = 32 To 923 Step 9
    With Range("DQ" & r)
        .Formula = "=HH" & r - 1 & "+HH" & r & "+HH" & r + 3 & "+HH" & r + 4 & ""
        .Value = .Value
    End With    
    With Range("DO" & r & ":DT" & r)
        .Formula = "=IF(DO" & r & "="""",0,If(CE" & r - 1 & "+DO" & r & " > DO" & r + 3 & ",B" & r & ",If(CE" & r - 1 & "+DO" & r & " < DO" & r + 3 & ",B" & r + 4 & "*-1,0)))"
        .Value = .Value
    End With
Next r
End Sub

 

[newshound12]

It works perfectly after I made some minor adjustments for additional criteria.
Thanks a lot.
Bet you got an A in Algebra!

 

[Michael M]

Actually....I really sucked at Algebra.....AND failed in Logic....go figure..
Amazing what 50 years will do to your mind !!!