if either cell is blank then....else

[pete39]

I have cells B6 and C6. I need vba code that will put a blank in I6 if either B6 or C6 is blank (has no data/is empty) or will add the value of B6 and C6 if both cells have a number. I have tried IfEmpty, vbNull String and many other post but nothing works. I just tried working with B6 alone (a number or blank) and can't write working code. Help

 

[Tom Urtis]

Is there are reason why you don't use a formula?

=if(OR(len(B6)=0,len(C6)=0),"",B6+C6)

In VBA:

With range("I6")
If isempty(Range("B6")) = true or isempty(Range("c6")) = true then
.clear
else
.Value = range("B6").Value + Range("C6").Value
end if
End With

 

[pete39]

Tom,
The code works if B&C6 have data but if B6 or C6 are blank ( I put cursor on B or C 6 and hit space) I get type mismatch when I run the code. Have something to do with how I clear the cell?

 

[Tom Urtis]

It sure does have something -- make that everything -- to do with the error you saw. Cells' contents are not cleared by hitting the spacebar. In that case, the cell's value is a spacebar character, which like any non-numeric character cannot be an element of a numeric mathematical operation.

This should cover your bases, including if a letter or some non-numeric character is in either B6 or C6. And by all means, select a cell and hit the Delete key when you want to clear its contents, not using the spacebar to do it.

Sub test()
With Range("I6")
If IsEmpty(Range("B6")) = True Or IsEmpty(Range("c6")) = True Then
.Clear
Else
If (Len(Range("B6").Value) > 0 And IsNumeric(Range("B6").Value) = False) _
Or (Len(Range("C6").Value) > 0 And IsNumeric(Range("C6").Value) = False) Then
MsgBox "There are not true numbers in either B6 or C6.", _
48, "Cannot calculate non-numeric entries."
Exit Sub
Else
.Value = Range("B6").Value + Range("C6").Value
End If
End If
End With
End Sub

 

[pete39]

Tom,
Since I'm giving this program to someone to use, I have to cover the case where the operator could clear a cells contents by hitting the space bar or backspace or delete. Does your code cover those cases or do I need different code. Thanks setting me straight. I've been working on this for hours and was beginning to think my VBA complier was bad when it was me. Well I'm learning!!!

 

[Tom Urtis]

OK, for the worksheet where this is happening, right-click on that sheet tab, left-click to select View Code, and paste the following code into the large white pane that is the worksheet module. Press Alt+Q to return to the worksheet. Should curtail most of the beginners' creative attempts at circumvention.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B6:C6")) Is Nothing Then Exit Sub
If IsEmpty(Target) Then Exit Sub
Select Case True
Case Left(Target.Value, 1) = Chr(32)
MsgBox "Do not use the spacebar to clear the cell contents." & vbCrLf & _
"Select the cell and press the Delete key instead.", 64, "You hit the spacebar."
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
Case IsNumeric(Target.Value) = False
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
MsgBox "Only numbers are allowed in cell " & Target.Address(0, 0), 48, "FYI"
End Select
End Sub

 

[pete39]

Tom
I guess I could go through the array and convert any space entries to blanks before I run the if statement

Also here is the code I was using but couldn't get I6 to show blank so I put in 400 in place of "" and then deleted it

Range("I6").Formula = "=if(and(isnumber(b6),isnumber(c6)),b6+c6,400)"