VBA - Sub select all, but add exceptions

[cozzagiorgi]

Hi

I have this sub wich transforms all my unchecked boxes in checked boxes (R) in column B.

Sub allewählen()
 
Dim i As Long
Dim lastrow As Long
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
 
    Range("B12:B" & lastrow).Value = "R" 'R = checkedbox
 
End Sub

How can I add exceptions to this sub?

Some rows will contain the value “S” and those should not be touched by this sub.

 

[My Aswer Is This]

You said:

R = checkedbox
​

What is a checkedbox??

Do you mean you have a Activex Checkbox assigned to each row and if it is checked do this?

 

[cozzagiorgi]

No, R is the value in the cell. As the Font in those Cells is Wingdings2, R equals to a symbol like a checked box. No activeX elements used.

 

[My Aswer Is This]

Since you did not say what you want to do if Column B equals R

Try this:
Replace do something with what you want to happen if column B equals R

Sub Test()
'Modified 7/9/2018 3:45 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim lastrow As Long
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 12 To lastrow
If Cells(i, "B").Value = "R" Then
'Do something
Next
Application.ScreenUpdating = True
End Sub

 

[cozzagiorgi]

i don't think we got that right.
The code in my first post puts an "R" in all selected cells (R is looking like a checked box in Wingdings2) when I click on a button.
What I now want to do is, when I click on the button it still puts "R" in all cells, except those who contain "S" (S is looking like a crossed checkbox in Wingdings2).

Putting that "R" only in the correct boxes is all I want.

 

[My Aswer Is This]

i don't think we got that right.
The code in my first post puts an "R" in all selected cells (R is looking like a checked box in Wingdings2) when I click on a button.
What I now want to do is, when I click on the button it still puts "R" in all cells, except those who contain "S" (S is looking like a crossed checkbox in Wingdings2).

Putting that "R" only in the correct boxes is all I want.

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[Fluff]

How about

Sub allewählen()
   Dim lastrow As Long
   lastrow = Cells(Rows.Count, "B").End(xlUp).Row
   With Range("B12:B" & lastrow)
      .Value = Evaluate(Replace("if(@=""S"",""S"",""R"")", "@", .Address))
   End With
End Sub

 

[cozzagiorgi]

That works! Can you explain what evaluate (replace) does?

 

[Fluff]

Replace simply replaces the @ sign with .address, whilst Evaluate calculates the worksheet formula

 

[cozzagiorgi]

Hm... sorry I can't follow