Quick Coding Question

[Desu Nota from Columbus]

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

This macro looks at A1 and then hides the rows based on the cases I created. I understand this, but now I want to apply a macro to a look at all values in column A.

How do I make a macro continue down column A until the first blank ? For instance, I want it to add cell B# and C# if A# < .85?

 

[Andrew Poulsom]

Example:

Sub Test()
    Dim LR As Long
    Dim r As Long
    With Sheet1
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        For r = 1 To LR
            Select Case .Range("A" & r).Value
                Case Is < 0.85
'                   Your code here
            End Select
        Next r
    End With
End Sub

 

[Desu Nota from Columbus]

Example:

Sub Test()
    Dim LR As Long
    Dim r As Long
    With Sheet1
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        For r = 1 To LR
            Select Case .Range("A" & r).Value
                Case Is < 0.85
'                   Your code here
            End Select
        Next r
    End With
End Sub

Andrew,

Thanks for the help. If you could answer a few questions for me I would most appreciative.

1) For the Your code here: How do I refer to the actions I want to take place? For instance, if I want to say add B and C together and place result in D do I put:

Range("D").FormulaR1C1 = "=sum(RC[-2]:RC[-1])"


2) What is the logic behind the indents for each line of code?

3) And say I wanted multiple events to happen when the case is true, how would I add another action?
----> Range("F").Value = "yes "

Puts a yes in all cells that meet the case.

 

[rguy84]

re 2- Indenting code allows you to see what blocks of statements are together.

 

[Andrew Poulsom]

It would be:

Range("D" & r).FormulaR1C1 = "=sum(RC[-2]:RC[-1])"
Range("F" & r).Value = "yes "

Indenting just makes the code more readable. The indents are within each construct.