Finding "CL" or "CLOSED" in one clumn and changing to 99% in Another

[Guzzlr]

For i = 9 To LastRow
If UCase(Cells(i, "F")) <> "CL" Or UCase(Cells(i, "F")) <> "CLOSED" _
And Cells(i, "B").Value = 100 Then
Cells(i, "B").Value = 99 _
And Cells(i, "E").Formula = "=(E-.3)"
End If
Next i

Hello All...
I have a little problem here.
What I'm trying to accomplish, is for the macro to not find either "CL" or "CLOSED" (upper case should not matter) in column "F" and a 100% in column "B", and changing 100% in Column "B" to a 99%, and then subtracting a .3 from the number in Column E...one row at a time.
The loop is working correctly.

Column "B" is formatted previously with:

With Range("B9:B6000")
        .Formula = "=1-(C9/D9)"
        .NumberFormat = "0%"
End With

What's happening, is every cell in Column "B", as the loop moves row-to-row, changes the number in column "B", regardless if it's 100% to 0%, and Column "E" number is not getting .3 subtracted.

Where am I going wrong this time!
Thanks for the help
excel 2013

 

[Fluff]

Yes, I have "On Error Resume Next", at the beginning.

Then remove it. That is about the worst thing you can do.
All it does is mask any errors in the code & can result in the code working incorrectly as it is here.

 

[Guzzlr]

I removed on error resume next, and it gives me a Type Mismatch on this line of code, when it hits the first line with #N/A

   If UCase(Cells(i, "F")) <> "CL" And UCase(Cells(i, "F")) <> "CLOSED" _
      And Cells(i, "B").Value = 1 Then
         Cells(i, "B").Value = 0.99
         Cells(i, "E") = Cells(i, "E") - 0.3
   End If

 

[Guzzlr]

   If Not Application.IsError(Cells(i, "F")) Then
   If UCase(Cells(i, "F")) <> "CL" And UCase(Cells(i, "F")) <> "CLOSED" _
      And Cells(i, "B").Value = 1 Then
         Cells(i, "B").Value = 0.99
         Cells(i, "E") = Cells(i, "E") - 0.3
   End If
    End If

I put the IsError in, which solved the any error part, instead of just looking for #N/A. But it's still is giving me a "Type Mismatch" fault on:

If UCase(Cells(i, "F")) <> "CL" And UCase(Cells(i, "F")) <> "CLOSED" _
      And Cells(i, "B").Value = 1 Then

Thanks for the help

 

[Fluff]

That's because you have an error in col B.
Try

   If Not IsError(Cells(i, "B").Value) Then
      If UCase(Cells(i, "F")) <> "CL" And UCase(Cells(i, "F")) <> "CLOSED" _
         And Cells(i, "B").Value = 1 Then
            Cells(i, "B").Value = 0.99
            Cells(i, "E") = Cells(i, "E") - 0.3
      End If
   End If

If you also have formulaic errors in col F, you'll need to add that in as well

 

[Guzzlr]

A formulaic error?

 

[Fluff]

An error returned by a formula

 

[Guzzlr]

An error returned by a formula

     ' Compute V-Lookup for Finding Closed Status *************************
    With Range("F9:F6000")
        Range("F9:F6000") = Application.WorksheetFunction.VLookup(Sheets("Template").Range("G9:G6000"), _
        Sheets("Template").Range("H9:AQ6000"), 6, False)
        .NumberFormat = "0.0"
    End With

I was thinking this is what you mean, because this is text going into column F, so the number format would not matter.



Also, thanks for finding my mistake on the Type Mismatch error for the error in Column B and not Column F.
I changed it to Column B and it works fine.

If Not IsError(Cells(i, "B").Value) Then
      If UCase(Cells(i, "F")) <> "CL" And UCase(Cells(i, "F")) <> "CLOSED" _
         And Cells(i, "B").Value = 1 Then
            Cells(i, "B").Value = 0.99
            Cells(i, "E") = Cells(i, "E") - 0.3
      End If
End If

 

[Fluff]

Glad to help & thanks for the feedback

 

[Guzzlr]

[TABLE="width: 515"]
<tbody>[TR]
[TD="class: xl506, width: 103, align: center"]100%
[/TD]
[TD="class: xl507, width: 103, align: center"]0.1
[/TD]
[TD="class: xl507, width: 103, align: center"]85.2
[/TD]
[TD="class: xl507, width: 103, align: center"]85.1
[/TD]
[TD="class: xl507, width: 103, align: center"]QA
[/TD]
[/TR]
</tbody>[/TABLE]

Getting back to this. Here's an example of how the code, for some reason, skips over "QA" in Column F.
The 100% in column B should have changed to 99% and 85.1 in Column E should have changed to 84.8. This only happens with "QA" in Column F, everything else works correctly.
What is it with the letters "QA" to cause excel not to run the code?
Thanks for the help

 

[Fluff]

Check the value in col B, it probably isn't 1.