Help with VB code

[Desu Nota from Columbus]

I'm trying to learn to write VB code and am currently trying to figure out how to turn =if(and()) statements into VB code (also nestedif)

Criteria: If A1>=.85, hide rows 2-10
If A1<.85, hide rows 10-20

I don't know how to link both if statements.

Sub testing_1()

If Sheet1.Range("A1") >= .85 Then
  Range("2:10").EntireRow.Hidden = True
Else
If Sheet1.Range("a1") < .85 then
  Range("10:20").EntireRow.Hidden = True
End If
End Sub

Is this correct?

 

[Comfy]

almost

Sub testing_1()

If Sheet1.Range("A1") >= .85 Then
  Rows("2:10").EntireRow.Hidden = True
Elseif Sheet1.Range("a1") < .85 then
  Rows("10:20").EntireRow.Hidden = True
Else
End If
End Sub

You could also use Select statements.

Sub testing_1()

Select Case Sheet1.Range("A1").Value
Case < 0.85
 Rows("10:20").EntireRow.Hidden = True
Case => 0.85
 Rows("2:10").EntireRow.Hidden = True
Case Else
End Select
End Sub

 

[Rick Rothstein]

The code Comfy gave you will work and it is how you would set things up if you had more than two conditions to test (you would continue to add ElseIf statement blocks as needed)... see that Else statement at the end of his code... that is where the catch-all code would go; that is, the code to cover any conditions not covered by the If or ElseIf statement blocks that come before it. In your situation, there are only two conditions... values in A1 the are equal to or greater than 0.85 or those values in A1 less than 0.85 which is actually everything else. So, we can write your code more simply than Comfy shows it like this...

Sub testing_2()
    If Sheet1.Range("A1") >= 0.85 Then
        Rows("2:10").EntireRow.Hidden = True
    Else
        Rows("10:20").EntireRow.Hidden = True
    End If
End Sub

EDIT

I see Comfy has edited his response to show you a Select Case method also. For the same reasons as above, we can use the Case Else to handle the second Case statement (which is the everything else) handler...

Sub testing_2()
    Select Case Sheet1.Range("A1").Value
        Case Is < 0.85
            Rows("10:20").EntireRow.Hidden = True
        Case Else
            Rows("2:10").EntireRow.Hidden = True
    End Select
End Sub

 

[Rick Rothstein]

I would have re-edited my last message to include the following, but my time-limit for editing a respons expired.

I just noticed a couple of thing about your code and my modifications to it. First, as structured, the code will not work correctly if you edit the value in A1 in such a way that edited value is on the other side of 0.85 as the existing value is (for example, changing the value from 0.7 to 0.9). That is because previously hidden rows will remain hidden. I also noticed that you qualified your Range property call but not your Rows property calls... if you need to qualify one, then you should qualify them all. Finally, you do not have to include the EntireRow property call that you attached to the Rows property call since the Rows property call automatically includes the entire row. Combining all of these comments yields the following modified code to what I posted earlier...

Sub testing_2()
    Rows("2:20").Hidden = False
    If Sheet1.Range("A1") >= 0.85 Then
        Sheet1.Rows("2:10").Hidden = True
    Else
        Sheet1.Rows("10:20").Hidden = True
    End If
End Sub

Sub testing_2()
    Rows("2:20").Hidden = False
    Select Case Sheet1.Range("A1").Value
        Case Is < 0.85
            Sheet1.Rows("10:20").Hidden = True
        Case Else
            Sheet1.Rows("2:10").Hidden = True
    End Select
End Sub

 

[Comfy]

The reason I included the Else without any code was just incase non numerical values were in A1. You could include error checking etc but felt it not required at this time.

 

[Desu Nota from Columbus]

Thanks for the reply and help--I like that select statement much more. Is there a limit to the amount of cases?

And is there a method to the spacing (indents)?

Now, how do I change font/cell shading in what I like to call the command line following the case criteria?

Something like:

blah blah blah
     Case Is >= .85
            Sheet1.Range("A2:D4").Font.Color = 6
     Case Else
End Select

End If

 

[Desu Nota from Columbus]

In Advance: thank you all who help me debunk the mystery that is coding.

Hopefully in the next 2 weeks I will be able to successfully teach myself through the vesicle of MrExcel, how to use VB and write macros w/o recording steps.


Can anyone please explain the R and C notation used in VB? I don't understand this:

Range("N2").Select
    ActiveCell.FormulaR1C1 = "=(RC[-5]+RC[-1])/3600"

My assumption is the the "R" is just the absolute reference marker, but I don't quite comprehend the C[+/-#] notation

 

[Desu Nota from Columbus]

The reason I included the Else without any code was just incase non numerical values were in A1. You could include error checking etc but felt it not required at this time.

Thanks for clarifying, I wasn't quite sure. Any explanation as to why you add something will help me tremendously.

And just sticking to basics will help the wall to my left from having a head slammed repeatedly into it

 

[Rick Rothstein]

I also noticed that you qualified your Range property call but not your Rows property calls... if you need to qualify one, then you should qualify them all.
.....
.....

Sub testing_2()
    Rows("2:20").Hidden = False
    ....
    ....

Sub testing_2()
    Rows("2:20").Hidden = False
    ....
    ....

Wouldn't it have been nice if I had taken my own advice... I just noticed that I forgot to qualify the Rows property calls in the lines of code I added to the each of the code procedures.

Sub testing_2()
    Sheet1.Rows("2:20").Hidden = False
    If Sheet1.Range("A1") >= 0.85 Then
        Sheet1.Rows("2:10").Hidden = True
    Else
        Sheet1.Rows("10:20").Hidden = True
    End If
End Sub

Sub testing_2()
    Sheet1.Rows("2:20").Hidden = False
    Select Case Sheet1.Range("A1").Value
        Case Is < 0.85
            Sheet1.Rows("10:20").Hidden = True
        Case Else
            Sheet1.Rows("2:10").Hidden = True
    End Select
End Sub