CODEWITHSUNDEEP
excelvba
Kbee
Kbee

Reputation: 27

IF/THEN using IsEmpty

Probably doing something wrong here, so if I am please let me know!

What I'm trying to accomplish:

enter image description here

My attempt to translate this into VBA is below. Note, I've added the "This Cell Should Be Empty" string to visualize if this function is doing anything temporarily. I will want to delete that.

Any suggestions to accomplish my intention here?

Sub IsEmptyRange()

Dim myRange As Range
Dim cell As Range
Dim myOutput As Range


Set myRange = Range("C1:C10")
Set myOutput = Range("U1:U10")

For Each cell In myRange.Cells
  
    If IsEmpty(cell) Then
   
    myOutput.Value = "This Cell Should Be Empty"
        
    ElseIf IsEmpty(cell) = False Then
    
    myOutput.FormulaR1C1 = "=RC13+RC14+RC16+RC18"
    End If
Next cell
  

End Sub

Upvotes: 0

Views: 538

Answers (1)

Skin
Skin

Reputation: 11197

Look at using the IsNumeric function along with Len ...

Public Sub TestForSixDigitNumber()
    Dim strValue As String
    
    strValue = "123456"
    
    If IsNumeric(strValue) And Len(strValue) = 6 Then
        MsgBox "Is a 6 digit number", vbInformation, "Success"
    Else
        MsgBox "Is NOT a 6 digit number", vbCritical, "Error"
    End If
End Sub

A word of caution though, decimal places aren't factored in here.

Alternatively, you could bake it into your formula directly, something along the lines of (changing the reference to A1) ...

=IF(AND(ISNUMBER(A1), LEN(A1) = 6), Add your values, leave it alone)

Adapt as you see fit.

Upvotes: 1

Related Questions