If range cells contains any numbers or blank cells, then

Question

I am trying to create a VBA project like this, but I'm having a hard time using the LIKE function and nothing seems to happen when I hit the run button.

What I'm trying to do:

• If the first digit is either a number or a blank cell in B4:B245, then enter a text in range D4:245.
• If the last digit of the numeric is even in C4:C245, then enter a text in range D4:D245.

More info:

• Product codes were imported
• LEFT function was used to find the "First digit of product code"
• RIGHT function was used to find the "Numeric digits of product code"

My current position in excel and VBA:

Sub number()

Dim first As Range
    Set first = Range("B4:B259")

Dim numeric As Range
    Set numeric = Range("C4:B259")

Dim DColumn As Range
    Set DColumn = Range("D4:D259")

For Each first In DColumn
    If first Like " " Then
        DColumn = "Invalid Part Number"
        DColumn.Interior.ColorIndex = 6
End If
Next

End Sub

Answer 1

The below macro will perform 3 tests & each will get it's own output.

1. Check for Numeric or blank first character
2. Check for Even ending character
3. Check for Odd ending character

These test are not in unison - the output will be one, or none. As soon as a test statement is TRUE, the loop will end for that cell and other values will not be tested.

For example, this macro will not provide you outputs when #1 & #2 from above are true. It will only tell you if #1 is true.

---

This code does not require you to split the product codes. The macro will work with them as is

Sub MyNum()

Dim xCell As Range, Product_Code As Range
Set Product_Code = Sheets("Sheet1").Range("A2:A9") '<-- Update sheet name

For Each xCell In Product_Code
    If IsNumeric(Left(xCell, 1)) Or Left(xCell, 1) = " " Then
        xCell.Offset(0, 1) = "Invalid Product: Char 1 = Numeric or Null"
    ElseIf Right(xCell, Len(xCell) - 1) Mod 2 = 0 Then
        xCell.Offset(0, 1) = "Even Ending Range"
    ElseIf Right(xCell, Len(xCell) - 1) Mod 2 <> 0 Then
        xCell.Offset(0, 1) = "Odd Ending Range"
    End If
Next xCell

End Sub