VBA Enter Formula in blank cell if adjacent is non blank

[Koza84]

Hello.

I am looking for VBA solution to loop through column A and to enter formula =Today() in all blank cells in range A2:A1000 if adjacent cell in column B is not empty. For example in table below formula would need to be inserted in cells A3;A7, because they are blank and adjacent cells B3:B7 are not empty.

Best regards


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]24/11/18[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]24/11/18[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

If column B is values rather than formulae try

Sub FillBlanks()
   On Error Resume Next
   With Range("B2", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
      .Offset(, -1).SpecialCells(xlBlanks).Formula = "=today()"
   End With
   On Error GoTo 0
End Sub

 

[Koza84]

If column B is values rather than formulae try

Sub FillBlanks()
   On Error Resume Next
   With Range("B2", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
      .Offset(, -1).SpecialCells(xlBlanks).Formula = "=today()"
   End With
   On Error GoTo 0
End Sub

works brilliant ty

 

[Fluff]

You're welcome & thanks for the feedback