Change cell string if cell IsEmpty

[I3atnumb3rs]

Hello,

I'm trying to make it so that if colum B cell is empty to change column P cell to cancelled, but I can't seem to make it work. please help!

Sub ChngProgUnassigned()




Dim Col As Variant
Dim BlankRows As Long
Dim LastRow As Long
Dim r As Long
Dim StartRow As Long


Col = "B"
StartRow = 1
BlankRows = 1


LastRow = Cells(Rows.Count, Col).End(xlUp).Row




With ActiveSheet
For r = LastRow To StartRow + 1 Step -1
If IsEmpty(Cells(r, Col)) = True Then
.Cells(r, Col + 14).Value = "CANCELLED"
End If
Next r
End With


End Sub

 

[Fluff]

If the cells in col B are truly empty try

Sub AddCancel()
   Range("B:B").SpecialCells(xlBlanks).Offset(, 14).Value = "CANCELLED"
End Sub

 

[I3atnumb3rs]

Perfect! Thanks!

 

[Fluff]

You're welcome

 

[I3atnumb3rs]

So little snag, when there's no string "CANCELLED" the code seems to break

 

[I3atnumb3rs]

Sorry I meant if all the fields are in B:B are filled it breaks.

 

[Fluff]

In that case use

Sub AddCancel()
   On Error Resume Next
   Range("B:B").SpecialCells(xlBlanks).Offset(, 14).Value = "CANCELLED"
   On Error GoTo 0
End Sub

 

[Rick Rothstein]

In that case use

Sub AddCancel()
   On Error Resume Next
   Range("B:B").SpecialCells(xlBlanks).Offset(, 14).Value = "CANCELLED"
   On Error GoTo 0
End Sub

You can still do this with a one-liner ...

[table="width: 500"]
[tr]
	[td]Sub AddCancel()
  Range("B1", Cells(Rows.Count, "B").End(xlUp)).Replace "", "CANCELLED", xlWhole, , , , False, False
End Sub[/td]
[/tr]
[/table]

 

[Fluff]

One slight problem with that, "CANCELLED" goes is col P not B

 

[Rick Rothstein]

One slight problem with that, "CANCELLED" goes is col P not B

Whoops!

A little messy, but it is still a one-liner...

[table="width: 500"]
[tr]
	[td]Range("B1", Cells(Rows.Count, "B").End(xlUp)).Offset(, 14) = Evaluate(Replace("IF(B1:B#="""",""CANCELLED"",IF(P1:P#="""","""",P1:P#))", "#", Cells(Rows.Count, "B").End(xlUp).Row))[/td]
[/tr]
[/table]