IF/THEN using IsEmpty-CodePudding

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

What I'm trying to accomplish:

Loop through the cells in Column C, if there is a 6 digit number in the cell, then I want a formula to be added to the same row in Column U that adds the values in Columns M,N,P and R. If there is not a 6 digit number in the cell of Column C, then leave the cell in Column U alone.

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

CodePudding user response：

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.