AND Condition

[sevenfive85]

Hi all,

Can someone please help with this conditional statement? I need two things to be true for the word "Select" to appear, Cell D3 must be "Yes" and C8 must contain something. Code below is what I have but it is wrong, please help. Thank you.


If Target.Address = "$D$3" Then
If Target.Value = "Yes" And Range("C8").Value <> "" Then
Range("K8:K300").Value = "Select"
Else
Range("K8:K300").Value = ""
End If
End If

 

[bobsan42]

How is it wrong?
Can you post the complete code? And explain how you expect it to work.
And try to avoid merged cells unless you really need them they ca be apita sometimes.

 

[offthelip]

I presume this is in your worksheet change event code so try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$3" Then
    Application.EnableEvents = False
   
    Range("K8:K300").Value = ""
    If Target.Value = "Yes" Then
        inarr = Range("c8:c300")
        outarr = Range("K8:K300").Value
        For i = 1 To UBound(inarr, 1)
         If inarr(i, 1) <> "" Then
           outarr(i, 1) = "Select"
         End If
        Next i
        Range("K8:K300").Value = outarr
    End If
    Application.EnableEvents = True

End If
End Sub

 

[bobsan42]

Aaah, i think i get it.
You need Kx to display Select if you have a value in Cx. But the problem is you only check for C8.
So you need to loop through all C cells and then set a value for each K cell. Or you can do with a simple formula in column K.

 

[bobsan42]

If Target.Address = "$D$3" Then
 If Target.Value = "Yes" Then
  For each cc in Range("C8:C300")
   If cc.Value <> "" Then
    Range("K"&cc.row).Value = "Select"
   Else
    Range("K"&cc.row).Value = ""
   End If
  Next cc
 Else
  Range("K8:K300").Value = ""
 End if
End If

Or in K8

=If(And($D$3="Yes",C8<>""),"Select","")

 

[bobsan42]

BTW the code from offthelip will probably work much faster. Especially over larger datasets.

 

[sevenfive85]

Here is the complete code. Actually it is working but not the way I was hoping. It is checking cell C8 only and populating the word "select" from K8 to K300 regardless if the C column is empty. I need it to check cell C9, C10, C11,....etc. Empty cell in the C column should not have "select" on the K column. Hopefully that clear things up.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$3" Then
If Target.Value = "Yes" And Range("C8").Value <> "" Then
Range("K8:K300").Value = "Select"
Else
Range("K8:K300").Value = ""
End If
End If
End Sub

 

[offthelip]

Have you tried my code from post 3 I think it does exactly what you have requested and will be very fast, which is necessary in a worksheet change event otherwise it slows everything down on your workhseet

 

[sevenfive85]

Have you tried my code from post 3 I think it does exactly what you have requested and will be very fast, which is necessary in a worksheet change event otherwise it slows everything down on your workhseet

thank you guys!!! Both solution work perfectly

 

[bobsan42]

thank you guys!!! Both solution work perfectly

Glad we could help.
I would stick to a formula solution in such a case.
But in terms of code offthelip's solution is better.