IF / THEN statement mismatch

[andre232]

Hey guys,

Trying to get this to work but no matter how I try it I either get mismatch or syntax error. Hopefully it's an easy fix thanks in advance!

Sub Button3_Click()


Dim X As Range
Set X = Range("D1:D13")







If X = "Passed" Then
Rows("D1:D13").EntireRow.Hidden = True

End If


End Sub

 

[Joe4]

This only works is X is a single cell:

[COLOR=#333333]If X = "Passed" Then[/COLOR]

But you are defining X to be a whole range of cells:

[COLOR=#333333]Set X = Range("D1:D13")[/COLOR]

So what is it exactly that you are trying to do?
Are you trying to check if ANY cells in that range is "Passed", or are you wanting to check if ALL of the cells in that range are "Passed"?

 

[Fluff]

As X is a range of cells it cannot = passed.
Try

Sub Button3_Click()
   Dim X As Range
   For Each X In Range("D1:D13")
      X.EntireRow.Hidden = LCase(X.Value) = "passed"
   Next X
End Sub

 

[andre232]

D1:D13 will either have "Passed" or "Failed" written in it based off a formula in the cells. I am trying to have the button look at D1:D13 and if all the cells say "Passed" then it should hide the range otherwise do nothing.

 

[MartyS]

Hi,

Not entirely sure what you're trying to do...

You've created a RANGE object called X

You've set the range object to the range of D1:D13

You've then looked to see if something = "Passed".


If your range was a single cell you could look to see whether the .value parameter of the Range object (e.g. Range("D1").value ) is equal to something (e.g. "Passed").

Here you're attempting to see if the entire range object is equal to some text.


One way of looking at it might be to think of it akin to asking if: {100, 105, 34, 25, "gooseberry", =A1+D1, 4, 56, 25, 564, 23, 46, "hello"} = "Passed"...
but still, it's the object you're looking to see equal something in the code you've supplied.



Are you attempting to see if every cell in the range D1:D13 says "Passed" and if so hide the entire row for those 13 cells?

Are you attempting to see which cells in the range D1:D13 says "Passed" and hide only those cells?

Or something else??

 

[Fluff]

Try this

Sub Button3_Click()
   Dim X As Range
   Set X = Range("D1:D13")
   X.EntireRow.Hidden = Application.CountIf(X, "Passed") = 13
End Sub

 

[andre232]

That worked perfectly! Thanks a lot!

 

[Fluff]

Glad we could help & thanks for the feedback