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

 

[oldbrewer]

in quick basic style
(are you looking for col B = 100 or <>100)

for i=9 to 100
if cells(i,6)<>"CL" and cells(i,6) <>"closed" then goto 50 else goto 100
50 if cells(i,2)=100 then cells(i,2)=99:cells(i,5)=cells(i5)-.3
100 next i

would need a end of data detector....

 

[Fluff]

Another option

For i = 9 To 11
   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
Next i

 

[Guzzlr]

Fluff
I'm not following i9 to 11.
Why would I not want to find last row?
Thanks

 

[Fluff]

I forgot to change the 11 to lastrow, after testing.

 

[Guzzlr]

For i = 9 To LastRow
   If Not Application.IsNA(Cells(i, "F")) Then
      If UCase(Cells(i, "F")) = "CL" Or UCase(Cells(i, "F")) = "CLOSED" Then
         Cells(i, "B").Interior.Color = RGB(197, 224, 179)
      End If
   End If
   

   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
   
   
   If Cells(i, "G").Value = 0 Then
      Range("B" & i & ":G" & i).Clear
      Intersect(Rows(LastRow + 1 & ":" & Rows.Count), Range("B:F")).Clear
   End If
Next i

So Here's my entire code for what I'm doing. It's almost working, but for some reason, if an error is in column B and the letters "QA" are in column F, then the code inserts 99% in Column B and subtracts the .3 from Column E. I don't know why the code is fixated on an error (any error) in Column B and the letters "QA" in column F.
Other than that It appears to be working.
PS. the code looks for "CL" And "Closed" in column F. However, there will never be "CL" And "Closed" at the same time in Column F, only one or the other, which is why I originally had Or. But Or inserts 99% in Column B regardless of the number. So, I'm not following that logic.
Thanks for the help

 

[Fluff]

What do you mean by

if an error is in column B

Could you give some examples?

As for the logic on CL or Closed.
Using Or will return TRUE if either values is TRUE, as a cell cannot equal both CL AND CLOSED then the expression will always return TRUE..

 

[Guzzlr]

Could you give some examples?

B
77%
C
28.2
D
124.5
E
96.3
F
WIP
G
160754.00












0%48.048.00.0QA2216432.00
0%45.645.60.0MF2217910.0041%7.713.05.3MF2232558.00100%0.0159.5159.5Closed161988.00









































99%0.30.0-0.3QA2233885.0099%0.30.0-0.3QA2233886.00#DIV/0!0.00.00.0QA2233898.00
#DIV/0!0.00.00.0QA2233899.00#DIV/0!0.00.00.0QA2233901.00
#DIV/0!0.00.00.0QA2233902.00

Not sure how this will look. At any rate, at the top, the code found "Closed" and colored the 100% green.
All the blank spaces, the code is recognizing "0" in Column G, and deleting B:G, like it should.
The last block, the code is seeing an error, and seeing QA, and inserting 99% and subtracting .3 from column G. What I'm trying to accomplish, is if there is "CL" or "Closed" in Column F And 100% in Column B, then Column B will change to 99% and .3 subtracted from the current number in Column E.
Thanks for the help

 

[Fluff]

Do you have this line of code anywhere in your macro

On Error Resume Next

 

[Guzzlr]

Do you have this line of code anywhere in your macro

On Error Resume Next

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

* The code found the "Closed" in Column F, and inserted 100% like it should
* The code found 0.00 in Column G, and deleted Range(B:G) like it should (all the blanks in the middle)
* Notice the bottom section, the code is seeing the DIV/0 error, and seeing the QA, and inserting 99%, and subtracting .3 from Column E. It does not matter which error there is, if an error is in Column B, and QA in Column F, then 99% is inserted and .3 is subtracted.
* Notice the QA in F2, the 0% remained, because there was not an error code in B2.
* What I'm trying to accomplish, is if there is a 100% in Column B, however, there is not a "CL" or "Closed" in Column F, then the 100% in Column B is changed to 99% and .3 is subtracted from the current number in Column E
Thanks for the help

B	C	D	E	F	G
77% <tbody> </tbody>	28.2 <tbody> </tbody>	124.5 <tbody> </tbody>	96.3 <tbody> </tbody>	WIP <tbody> </tbody>	160754.00 <tbody> </tbody>
0% <tbody> </tbody>	48.0 <tbody> </tbody>	48.0 <tbody> </tbody>	0.0 <tbody> </tbody>	QA <tbody> </tbody>	2216432.00 <tbody> </tbody>
0% <tbody> </tbody>	45.6 <tbody> </tbody>	45.6 <tbody> </tbody>	0.0 <tbody> </tbody>	MF <tbody> </tbody>	2217910.00 <tbody> </tbody>
41% <tbody> </tbody>	7.7 <tbody> </tbody>	13.0 <tbody> </tbody>	5.3 <tbody> </tbody>	MF <tbody> </tbody>	2232558.00 <tbody> </tbody>
100% <tbody> </tbody>	0.0 <tbody> </tbody>	159.5 <tbody> </tbody>	159.5 <tbody> </tbody>	Closed <tbody> </tbody>	161988.00 <tbody> </tbody>
99% <tbody> </tbody>	0.3 <tbody> </tbody>	0.0 <tbody> </tbody>	-0.3 <tbody> </tbody>	QA <tbody> </tbody>	2233885.00 <tbody> </tbody>
99% <tbody> </tbody>	0.3 <tbody> </tbody>	0.0 <tbody> </tbody>	-0.3 <tbody> </tbody>	QA <tbody> </tbody>	2233886.00 <tbody> </tbody>
#DIV/0! <tbody> </tbody>	0.0 <tbody> </tbody>	0.0 <tbody> </tbody>	0.0 <tbody> </tbody>	QA <tbody> </tbody>	2233898.00 <tbody> </tbody>
#DIV/0! <tbody> </tbody>	0.0 <tbody> </tbody>	0.0 <tbody> </tbody>	0.0 <tbody> </tbody>	QA <tbody> </tbody>	2233899.00 <tbody> </tbody>
#DIV/0! <tbody> </tbody>	0.0 <tbody> </tbody>	0.0 <tbody> </tbody>	0.0 <tbody> </tbody>	QA <tbody> </tbody>	2233901.00 <tbody> </tbody>
#DIV/0! <tbody> </tbody>	0.0 <tbody> </tbody>	0.0 <tbody> </tbody>	0.0 <tbody> </tbody>	QA <tbody> </tbody>	2233902.00 <tbody> </tbody>

<tbody>
</tbody>