Using Else for multiple IF's

[spiffmonkey1]

Hi,

Part of my formula is this-

Dim r As Long
For r = 4 To 10
'1 box colored G=G
If WorksheetFunction.CountIf(Range(Cells(69, r), Cells(80, r)), "yes") = 1 _
And WorksheetFunction.CountIf(Range(Cells(69, r), Cells(80, r)), "N/A") = 8 _
And WorksheetFunction.CountBlank(Range(Cells(69, r), Cells(80, r))) = 3 Then
Range(Cells(67, r), Cells(67, r + 1)).Interior.ColorIndex = 50
'1 box colored R=R
If WorksheetFunction.CountIf(Range(Cells(69, r), Cells(80, r)), "no") = 1 _
And WorksheetFunction.CountIf(Range(Cells(69, r), Cells(80, r)), "N/A") = 8 _
And WorksheetFunction.CountBlank(Range(Cells(69, r), Cells(80, r))) = 3 Then
Range(Cells(67, r), Cells(67, r + 1)).Interior.ColorIndex = 3

I want to know if there is a way to use Else or a similar statement where only if none of these If's work, then show "N/A". Because if I put the Else statement inside the IF's, then it'll show N/A if one of the IF statement is false.

Thanks

 

[ZPhantom]

have you considered using a Case statement?

It works like an if statement with multiple conditions (and outcomes) and would likely suit your needs perfectly

 

[Ruddles]

Cut & Keep

How to code IF statements

1) Simple IF with a single statement

   If condition Then x = 1

2) Simple IF with multiple statements

   If condition Then x = 1: a = ""

3) Block IF..THEN

   If condition Then
     x = 1
     a = "xxx"
   End If

4) Block IF..THEN..ELSE

   If condition Then
     x = 1
     a = "xxx"
   Else
     x = 2
     a = "yyy"
   End If

5) Block IF..THEN..ELSEIF

   If condition1 Then
     x = 1
     a = "xxx"
   ElseIf condition2 Then
     x = 2
     a = "yyy"
   End If

6) Block IF..THEN..ELSEIF..ELSE

   If condition1 Then
     x = 1
     a = "xxx"
   ElseIf condition2 Then
     x = 2
     a = "yyy"
   ElseIf condition3 Then
     x = 3
     a = "zzz"
   Else
     x = 4
     a = "xyz"
   End If

How to code SELECT statements

1) Single test variable

   Select Case variable
     Case 1, 3, 6 To 9
       x = 1
       a = "xxx"
     Case Is <= 100
       x = 2
       a = "yyy"
     Case Is <= 1000
       x = 3
       a = "zzz"
     Case Else
       x = 4
       a = "xyz"
   End Select

2) Multiple test variables

   Select Case True
     Case x < 100
       a = "xxx"
     Case a = "yyy", a = "zzz"
       x = 2
     Case Else
       x = 4
       a = "xyz"
   End Select

 

[spiffmonkey1]

Ok so my formula is like this-

Dim r As Long
For r = 4 To 20
'1 box colored G=G
If WorksheetFunction.CountIf(Range(Cells(69, r), Cells(80, r)), "yes") = 1 _
And WorksheetFunction.CountIf(Range(Cells(69, r), Cells(80, r)), "N/A") = 8 _
And WorksheetFunction.CountBlank(Range(Cells(69, r), Cells(80, r))) = 3 Then
Range(Cells(67, r), Cells(67, r + 1)).Interior.ColorIndex = 50
'1 box colored R=R
ElseIf WorksheetFunction.CountIf(Range(Cells(69, r), Cells(80, r)), "no") = 1 _
And WorksheetFunction.CountIf(Range(Cells(69, r), Cells(80, r)), "N/A") = 8 _
And WorksheetFunction.CountBlank(Range(Cells(69, r), Cells(80, r))) = 3 Then
Range(Cells(67, r), Cells(67, r + 1)).Interior.ColorIndex = 3
'2 box colored 2G=G
Else
Range(Cells(67, r), Cells(67, r + 1)) = "N/A"
End If
Next r
End Sub

The problem is that when i run the macro, the change in one cell pushes a change to the cell on the right of it even though the cell on the right of it does not meet the requirements. I am not sure how to use case statements but I can try and figure it out.

Thanks!

 

[spiffmonkey1]

I have another quick question. If I want r to be from 4 to the END of the column(because I have a table), how would I do that?

Thanks

 

[spiffmonkey1]

My macro is like this-
FinalColumn = Range(Cells(68, D), Cells(68, CE)).End(xlDown).Count
For r = 4 To FinalColumn

However, this is not working. I want r = 4 to go to FinalColumn. But I want the FinalColumn to equal the number of cells that contain a word in that row(Row 67) from D67. I tried CountA but didn't work. The range would be from D67 to the last cell that contains a word in the row.

Thanks!

 

[spiffmonkey1]

Figured it out...using countA
MOD close this thread please