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.
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.