VBA If sum value is outside of range

[cortexnotion]

Hi All

In need of your help with some VBA I can't get quite right!

The calculation is: (Column G+H) - (Column M+N). Then if the result is lower than -0.1 or greater than 0.1 apply the string value. What I have below is not generating the right results.

Any help appreciated, thanks

For VRes = 4 To LR3
     If WorksheetFunction.Sum((.Range("G" & VRes).Value + .Range("H" & VRes).Value) - (.Range("M" & VRes).Value - .Range("N" & VRes).Value)) < -0.1 Or _
        WorksheetFunction.Sum((.Range("G" & VRes).Value + .Range("H" & VRes).Value) - (.Range("M" & VRes).Value - .Range("N" & VRes).Value)) > 0.1 Then
                .Range("R" & VRes).Value = "Change Location to XYs " & .Range("G" & VRes).Value & ", " & .Range("H" & VRes).Value

 

[jasonb75]

The calculation is: (Column G+H) - (Column M+N).

You have (M-N) in your code, which is correct?

If Abs((.Range("G" & VRes).Value + .Range("H" & VRes).Value) - (.Range("M" & VRes).Value + .Range("N" & VRes).Value) + 1) > 0.1 Then
                .Range("R" & VRes).Value = "Change Location to XYs " & .Range("G" & VRes).Value & ", " & .Range("H" & VRes).Value

 

[cortexnotion]

Hi @jasonb75, I'm not following your reply sorry, I need to check if the sum results in either >0.1 or lower than -0.1, anything in between -.0.1 and 0.1 would result in no action.

thanks

 

[jasonb75]

You said

The calculation is: (Column G+H) - (Column M+N).

but the calculation in your code was (Column G+H) - (Column M-N) so is the quote correct or is the code correct?

The line of code that I added to my reply checks <-0.1 and >0.1 at the same time by making negative results positive so that you don't need to do everything twice.

 

[cortexnotion]

@jasonb75, apologies I didn’t catch onto what Abs function does. Many thanks for your help!