VBA_HELP

[Ravindra]

Hi All,

I am newbie in VBA programming. I am puzzled in below given query:-

There are three cells b1,b2, and b3 respectively for the input of three numbers i.e. number1,number2,and number 3 respectively. There are two cells b5 and b6 for the showing total and average respectively.

I have used one command button and used these functionality on the click-event of this.

Now, my problem is , I wan to validate three cells b1,b2, and b3 respectively for the left blank..if any cell is left blank then when I click on command button to get the total and average then it show some error message for any left blank cell.

For example:- I have put b1=10,b2 is blank,b3=10, and when I click on command button to get total and average, it must show error message for b2 is blank. Please help me.

thanks....

 

[Nebu]

Hi:

Here is a basic code:

you can do a lot of improvement on the same

Sub test()


If Application.WorksheetFunction.CountBlank(Sheet1.Range("B1:B3")) <> 0 Then
MsgBox "Cells are blank"
Else
Sheet1.[B5] = Application.WorksheetFunction.Sum(Sheet1.Range("B1:B3"))
Sheet1.[B6] = Application.WorksheetFunction.Average(Sheet1.Range("B1:B3"))
End If

End Sub

Thanks

 

[Smallman]

Hi Ravindra

Give the following a crack.

Option Explicit

Sub Testing()
Dim i As Integer

    For i = 1 To 3
        If Range("B" & i) = vbNullString Then MsgBox "B" & i & " blank": Exit Sub
    Next i
    'Your Calcs Here
End Sub

Take care

Smallman

 

[Ravindra]

Hi:

Here is a basic code:

you can do a lot of improvement on the same

Sub test()


If Application.WorksheetFunction.CountBlank(Sheet1.Range("B1:B3")) <> 0 Then
MsgBox "Cells are blank"
Else
Sheet1.[B5] = Application.WorksheetFunction.Sum(Sheet1.Range("B1:B3"))
Sheet1.[B6] = Application.WorksheetFunction.Average(Sheet1.Range("B1:B3"))
End If

End Sub

Thanks

Thanks...