• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to write a formula in column if cell not empty

aggie81

Member
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.
 

Attachments

Hui

Excel Ninja
Staff member
Aggie81

Try the following:

Code:
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

Member
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.
75955
I hope I have included enough for you to see what I've attempted.
 

Hui

Excel Ninja
Staff member
Code:
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

Excel Ninja
Staff member
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

Member
I got the code to run but there is a Debug box that returns.
75993
75995
The formula should go in the C36 cell.
Thank you for being patient with me and helping.
 

Hui

Excel Ninja
Staff member
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:

Code:
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...
 
Top