当前位置: 动力学知识库 > 问答 > 编程问答 >

excel vba- Traverse Column and display message box if value greater than *

问题描述:

i got the vba code to display msgbox by checking one value.

If Range("AH4").Value Then

MsgBox "tablespace greater than 95"

but i want to check a whole column of about 50 values and i want a looping code that traverses through the whole column and displays a msgbox if any of the value is greater than 95.

A B C D

1 Tablespace Total Size(Mb) Total Free(MB) PCT_USED

2 MONITOR 100 99 1

3 PSAPSR3 73,940 3,992 94.6

4 PSAPSR3702 84,960 24,391 71.29

5 PSAPSR3USR 40 16 61

6 PSAPUNDO 9,260 9,221 0

7 SYSAUX 760 51.3125 93

8 SYSTEM 1,470 9.75 99

Now i want to refine the solution given by chris.

1. I want my msgbox should display the actual value of the cell it finds greater than 95 under PCT_USED.

2. And that msgbox should display the name of the corresponding tablespace under the column A(i have freezed this column in my excel sheet).

网友答案:

You don't need to loop for this.

Try

Sub TestRange()
    Dim rng As Range

    Set rng = [F1:F10]  ' <-- adjust to your requirements
    If Application.WorksheetFunction.Max(rng) > 95 Then
        MsgBox "tablespace greater than 95"
    End If
End Sub
网友答案:

what about using a for each loop ....

Sub CheckInColumn()
Dim TestCell As Range

    For Each TestCell In [E1].EntireColumn.Cells
        ' break on first find
        If TestCell > 95 Then
            MsgBox "Co-coo"
            Exit For
        End If
    Next TestCell
End Sub

Instead of [E1] you could use Selection to check the column where your curser is currently positioned.

For Each loops are usually faster than discrete For ... Next loops.

分享给朋友:
您可能感兴趣的文章:
随机阅读: