Excel Marco: fill cells based on value in adjacent cell

[pkobzar]

Hello,
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:
macro.png

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.

Appreciate your help!

[Fluff13]

How about

Please Login or Register  to view this content.

[pkobzar]

it is just filling the column to infinity
macro.png

[Fluff13]

In that case column A is not blank. Do you have formulae in there?

[pkobzar]

yes, sorry. the job numbers are vlookup results in column A

[pkobzar]

I have this recommendation from another forum,
but it ignores the zero vlookup value in column A.
I want this macro to stop if adjacent is "0"

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

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

[Rick Rothstein]

Since your range is fixed in size, then this macro should work for you...

Please Login or Register  to view this content.

[Rick Rothstein]

I want this macro to stop if adjacent is "0"

Could you explain this in a little more detail (maybe show an example) as you did not mention this in your original post.

[pkobzar]

sure, this is my ultimate result: statuses are reset to SCHEDULED with a click of the Button,
but zeros in row 33 are ignored and status is not populated for cell E33
Columns B,C,D are vlookup values.

[pkobzar]

[solved] on another forum, but I'm too fresh here to post links, will update when I "grow up".
solution #1:

Please Login or Register  to view this content.

solution #2 (also works if adjacent cells are not Constants, but Formulas)

Please Login or Register  to view this content.

solution #3

Please Login or Register  to view this content.