VBA to write a formula in column if cell not empty

[aggie81]

I don't know VBA and asking for help on learning VBA to write a formula in column C if the cell in column A is not empty.
I have attached a workbook with the formula and the way I am using now is to put the formula in more cells than the user will use.
The user may add comments to explain what they want done by the people doing the work and each row doesn't need a formula based on the contents of column A.
I thought about using a Table format but it doesn't copy the formula if there user adds a blank line above.
Can VBA apply row formatting?

Any help is appreciated.
Thank you.

 

[Hui]

Aggie81

Try the following:

Sub x()

Dim LR As Integer
LR = Range("A" & Rows.Count).End(xlUp).Row

Dim c As Range
For Each c In Range(Cells(5, 1), Cells(6, 1))
   If c.Value <> "" Then c.Offset(0, 2).Formula = "=XLOOKUP(A" + CStr(c.Row) + ",Items[ITEMNO],Items[DESC],"""",0)"
Next c

End Sub

 

[aggie81]

I'm not sure I have done it correctly but I can't get it to work.
When I type the item number in A36 it should write the formula in C36 but I can't get it to work.

I hope I have included enough for you to see what I've attempted.

 

[Hui]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(ActiveCell, Range("A:A")) Is Nothing Then Exit Sub

If Target.Value <> "" Or Target.Value <> " " Then Target.Offset(0, 2).Formula = "=XLOOKUP(A" + CStr(c.Row) + ",Items[ITEMNO],Items[DESC],"""",0)"

End Sub

 

[Hui]

That code has to be in the Marciela Worksheet Object

You also have to save the file a s a *.xlsm or *.xlsb file type

 

[aggie81]

I got the code to run but there is a Debug box that returns.


The formula should go in the C36 cell.
Thank you for being patient with me and helping.

 

[Hui]

I have changed the Worksheet event that is used to trigger the code and added a few tweaks

Replace the existing code with the code below:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(ActiveCell, Range("A:A")) Is Nothing Then Exit Sub

Application.EnableEvents = False
On Error Resume Next

If Target.Value <> "" Or Target.Value <> " " Then Target.Offset(0, 2).Formula = "=XLOOKUP(A" + CStr(Target.Row) + ",Items[ITEMNO],Items[DESC],"""",0)"

Application.EnableEvents = True

End Sub

Enjoy...

 

[aggie81]

Thank you! It works very well.