Please make my day

[SvenVII]

Essentially this macro, is simply comparing cells in the same row, and based of the number two cells away from my selection it will provide a specific output. It then loops to the next cell and compares the cells in that row.

My issue, is that no matter what ever cell returns "Still not working", for the life of me I can't figure out what it keeps doing that.

Sub Corrections()
Dim Cell As Range
For Each Cell In Selection
If Cell.Offset(0, -23) = Cell.Offset(0, -27) _
And Cell.Offset(0, -8) = Cell.Offset(0, -12) _
And Cell.Offset(0, -7) = Cell.Offset(0, -11) Then
Cell.Value = "C"
ElseIf Cell.Offset(0, -2).Value = 21 _
And Cell.Offset(0, -23) <> Cell.Offset(0, -27) _
Or Cell.Offset(0, -22) <> Cell.Offset(0, -26) _
Or Cell.Offset(0, -21) <> Cell.Offset(0, -25) Then
Cell.Value = "Still Not Working"
ElseIf Cell.Offset(0, -2).Value = 31 _
And Cell.Offset(0, -23) <> Cell.Offset(0, -27) _
Or Cell.Offset(0, -22) <> Cell.Offset(0, -26) _
Or Cell.Offset(0, -21) <> Cell.Offset(0, -25) Then
Cell.Value = "RME"
ElseIf Cell.Offset(0, -2).Value = 36 _
And Cell.Offset(0, -23) <> Cell.Offset(0, -27) _
Or Cell.Offset(0, -22) <> Cell.Offset(0, -26) _
Or Cell.Offset(0, -21) <> Cell.Offset(0, -25) Then
Cell.Value = "NA"
ElseIf Cell.Offset(0, -2).Value = 32 _
And Cell.Offset(0, -23) <> Cell.Offset(0, -27) _
Or Cell.Offset(0, -22) <> Cell.Offset(0, -26) _
Or Cell.Offset(0, -21) <> Cell.Offset(0, -25) Then
Cell.Value = "RME"
ElseIf Cell.Offset(0, -2).Value = 22 _
And Cell.Offset(0, -23) <> Cell.Offset(0, -27) _
Or Cell.Offset(0, -22) <> Cell.Offset(0, -26) _
Or Cell.Offset(0, -21) <> Cell.Offset(0, -25) Then
Cell.Value = "UNDV"
ElseIf Cell.Offset(0, -2).Value = 37 _
And Cell.Offset(0, -32) <> Cell.Offset(0, -27) _
Or Cell.Offset(0, -22) <> Cell.Offset(0, -26) _
Or Cell.Offset(0, -21) <> Cell.Offset(0, -25) Then
Cell.Value = "NA"
ElseIf Cell.Offset(0, -23) <> Cell.Offset(0, -27) _
And Cell.Offset(0, -22) = Cell.Offset(0, -26) _
And Cell.Offset(0, -21) = Cell.Offset(0, -25) Then
Cell.Value = "ID"
ElseIf Cell.Offset(0, -23) = Cell.Offset(0, -27) _
And Cell.Offset(0, -22) <> Cell.Offset(0, -26) _
And Cell.Offset(0, -21) = Cell.Offset(0, -25) Then
Cell.Value = "ID"
ElseIf Cell.Offset(0, -23) = Cell.Offset(0, -27) _
And Cell.Offset(0, -22) = Cell.Offset(0, -26) _
And Cell.Offset(0, -21) <> Cell.Offset(0, -25) Then
Cell.Value = "ID"
End If
Next Cell
End Sub

 

[shg]

In Boolean logic, And is like mutiplication and Or is like addition with regard to operator precedence:

For example, in arithmetic, A*B+C is evaluated as (A*B) + C

Similarly, A And B Or C is evaluated as (A And B) Or C, not A And (B Or C)

I expect you may need some parens to guide evaluation, but having no idea what logic you're trying to apply, have no other suggestion.

 

[Norie]

Sven

I think you need to sort out the logic and flow of that code.

Will only one of the conditions in the If statements be satisfied for each cell in Selection.

Or is it possible that more than one could and there's some sort of precedence/order to what action is taken.

 

[SvenVII]

Only one scenario in this code can be true. To make sure, I tried to specify what conditions the code should run though by putting statments such as this one: Cell.Offset(0, -2).Value = 21

I did not know I could use parenthesis(sp?), I will try to put them were I think they might help and see where that gets me.

 

[Norie]

If only one can be true you should get rid of all the ElseIfs and just use a separate If fot each condition.

That would be a start I think, and perhaps makes things clearer.

Another advantage of doing that would be that you could step through the code with F8 and see what's actually happening.

 

[SvenVII]

Hmmmm, I'll see what that does. I didn't know about the F8 thing. I appreciate the help thus far.

 

[SvenVII]

I made every ElseIf statement its own IF-Then statement, but now the code says I have a For with no Next. I clearly have one at the end of the code.

 

[AlphaFrog]

You can get that error if you're missing an End If statement somewhere.

 

[Norie]

One more thing you could do is split the conditions out rather than doing them all on one line.

You might end up with a lot more if statements but that should make things clearer.

You never know if you do try that you might find some parts of the code that can be streamlined a bit.

There's actually a part of the code that might be done with a Select Case structure.

 

[AlphaFrog]

I'm not sure I got your criteria logic correct, but this is what I came up with...

Sub Corrections()
    
    Dim Cell   As Range, counter As Integer
    
    For Each Cell In Selection
        counter = 0
        If Cell.Offset(0, -23) = Cell.Offset(0, -27) Then counter = 1
        If Cell.Offset(0, -8) = Cell.Offset(0, -12) Then counter = counter + 1
        If Cell.Offset(0, -7) = Cell.Offset(0, -11) Then counter = counter + 1
        
        Select Case counter
            Case 3: Cell.Value = "C"
            Case 2: Cell.Value = "ID"
            Case 1
                Select Case Cell.Offset(0, -2).Value
                    Case 21: Cell.Value = "Still Not Working"
                    Case 22: Cell.Value = "UNDV"
                    Case 31 To 32: Cell.Value = "RME"
                    Case 36 To 37: Cell.Value = "NA"
                End Select
            Case 0
                'Do nothing?
        End Select
    Next Cell
    
End Sub