Fill cells based on value in adjacent cell

Question

I have this working macro button that will "reset all status" in the range B2:B15 to a default prompt for my colleagues to indicate job's status:

Private Sub CommandButton1_Click()

    Sheets("Sheet1").Range("B2:B15").Value = "please enter status"

End Sub

I want this macro to stop populating the value "please enter status" in the B column if there are no values in adjacent A column. Looking at screenshot it would leave B6:B15 empty. Column A values are vlookup results. If no result, vlookup returns zeros (currently hidden in white font with the help of conditional formatting)

Appreciate your help!

Answer 1

Maybe like this:

Private Sub CommandButton1_Click()
    Dim rng as Range
    Set rng = Sheets("Sheet1").Range("A2:A15")

    If Application.CountA(rng) > 0 Then
        rng.SpecialCells(xlCellTypeConstants).Offset(,1).Value = "please enter status"
    End If
End Sub

EDIT:

If the cells in the column are formulas, then change xlCellTypeConstants to xlCellTypeFormulas.

EDIT 2:

This is a simple way to do what you're looking for:

Private Sub CommandButton1_Click()
    Dim rng As Range
    Set rng = Sheets("Sheet1").Range("B2:B15")

    rng.Formula = "=IF(A2<>0,""please enter status"", """")"
    rng.Value = rng.Value
End Sub

Or with Evaluate:

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")

    Dim rng As Range
    Set rng = ws.Range("B2:B15")

    rng.Value = ws.Evaluate("IF(A2:A15<>0,""please enter status"", """")")
End Sub

EDIT 3: (3rd time's the charm?)

Another option would be to have the Vlookup return a blank string "" instead of 0 if there's no job number found.

Then you could leverage the 2nd parameter of Range.SpecialCells, like this (as proposed by @JvdV):

Private Sub CommandButton1_Click()
    Dim rng As Range
    Set rng = Sheets("Sheet1").Range("A2:A15")

    rng.SpecialCells(xlCellTypeFormulas, xlNumbers).Offset(, 1).Value = "please enter status"
End Sub

EDIT 4:

You can also make use of AutoFilter:

Private Sub CommandButton1_Click()
    With Sheets("Sheet1").Range("A1:B15")
        .AutoFilter 1, ">0"
            If .Cells.SpecialCells(12).Count > 2 Then .Offset(1).Resize(14, 2).Columns(2).Value = "Please enter status"
        .AutoFilter
    End With
End Sub