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

Guzzlr

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
982
Office Version
  1. 2021
Platform
  1. Windows
Code: 
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:
Code: 
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
 
Last edited:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
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....
 
Upvote 0
Another option
Code: 
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
 
Upvote 0
Fluff
I'm not following i9 to 11.
Why would I not want to find last row?
Thanks
 
Upvote 0
I forgot to change the 11 to lastrow, after testing.
 
Upvote 0
Code: 
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
 
Upvote 0
What do you mean by
if an error is in column B
Click to expand...
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..
 
Upvote 0
Fluff said:
Could you give some examples?
Click to expand...

HTML: 
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
 
Upvote 0
Do you have this line of code anywhere in your macro
Code: 
On Error Resume Next
 
Upvote 0
Fluff said:
Do you have this line of code anywhere in your macro
Code: 
On Error Resume Next
Click to expand...


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

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]77%
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]28.2
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]124.5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]96.3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]WIP[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl506, width: 109"]160754.00
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]0%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]48.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]48.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]0.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]QA[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl506, width: 109"]2216432.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]0%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]45.6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]45.6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]0.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]MF[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl506, width: 109"]2217910.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]41%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]7.7[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]13.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]5.3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]MF[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl506, width: 109"]2232558.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]100%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]0.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]159.5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]159.5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]Closed[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl506, width: 109"]161988.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]99%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]0.3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]0.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]-0.3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]QA[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl506, width: 109"]2233885.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]99%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]0.3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]0.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]-0.3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]QA[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl506, width: 109"]2233886.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]#DIV/0![/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]0.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]0.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]0.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]QA[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl506, width: 109"]2233898.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]#DIV/0![/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]0.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]0.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]0.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]QA[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl506, width: 109"]2233899.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]#DIV/0![/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]0.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]0.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]0.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]QA[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl506, width: 109"]2233901.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]#DIV/0![/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]0.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]0.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]0.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl506, width: 64"]QA[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl506, width: 109"]2233902.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Conditional Formatting Formula
Replies
5
Views
397
dreid1011
dreid1011
I
  • Question Question
Listbox columns dont alter when i set the width advice please
Replies
4
Views
44
ipbr21054
I
M
  • Solved
Sum the values between two specified column headings - number of columns between will change
2
Replies
14
Views
245
Moley84
M
M
  • Solved
return value based on the number that matches any number in three columns
Replies
3
Views
143
MMM_84
M
P
  • Question Question
putting a snippet of code from one to another somehow
Replies
5
Views
304
Patriot2879
P

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top