Insert value if Cell is not empty

[DaveUK]

Please could someone advise me how to do the following in VBA.

I need to check for an empty cell in column B from cell "B3" down until there are no more non-empty cells in the column.

If there is a value in a cell in the B column then i need to insert in column E on that self same row, yesterdays date in format "d-mmm-yyyy".
Also i need to insert "COMPLETE" in column N also on that self same row.

Please could someone advise me how to do this in VBA.

My workbook is called "BookCheck" and the worksheet is called "Inventory"

TIA

 

[erik.van.geit]

Hi, DaveUk,
what about

Sub test()
Dim LR As Long
Const FR As Integer = 3
LR = Range("B" & Rows.Count).End(xlUp).Row
    With Range("E" & FR & ":E" & LR)
    .FormulaR1C1 = "=IF(RC[-3],TODAY()-1,"""")"
    .Value = .Value
    End With
    With Range("N" & FR & ":N" & LR)
    .FormulaR1C1 = "=IF(RC[-12],""COMPLETE"","""")"
    .Value = .Value
    End With

End Sub

if you agree to overwrite existing values in columns E and N

kind regards,
Erik

 

[DaveUK]

Thanks for replying... I get "#value" in the cells.

Is it possible to use offset insted of [RC] as i find it easier to follow with offset!!

Totally confused me with all the """""""'s too!!

Thanks again

 

[Datsmart]

Eric,

I took the liberty to update your code:

Sub test()
Dim LR As Long
Const FR As Integer = 3
LR = Range("B" & Rows.Count).End(xlUp).Row
With Range("E" & FR & ":E" & LR)
.FormulaR1C1 = "=IF(RC[-3]<>"""",TODAY()-1,"""")"
.NumberFormat = "d-mmm-yyyy"
.Value = .Value
End With
With Range("N" & FR & ":N" & LR)
.FormulaR1C1 = "=IF(RC[-12]<>"""",""COMPLETE"","""")"
.Value = .Value
End With

End Sub

 

[Datsmart]

DaveUK

The double quotes are necessary when assigning formulae via VBA.
Using Offset would require the code to "Select" each cell, much slower than Eric's direct value assignment method.

Edit: spelling error...

 

[DaveUK]

OK

Many thanks for that information.

Thanks to you both for the help and advice.

 

[erik.van.geit]

fine you sorted it out

take care of your expressions
some guys take them literally (like me this time )

If there is a value in a cell in the B

since you were talking about VALUES I used
=IF(B1,"yes","no") instead of =IF(B1<>"","yes","no")
hence the error

I'll never code this way again :wink:
promise