Change formula excel to vba on format table

[afahruf90]

I have a .xlsm with format table. I am using formula excel, but sometimes my employee change that formula. Some body can teach me how to make VBA for my formula.
1.=IF([@[NO PO]]<>"",[@[NO PO]]&"-"&[@[LINE - NO PO]],"")
2.=IF([@[NO REG]]<>"",[@[NO REG]]&"-"&[@[LINE - NO REG]],"")
3.=IF([@[NO PO]]<>"",COUNTIF($E$8:[@[NO PO]],E8),"")
4.=IF([@[NO REG]]<>"",COUNTIF($S$8:[@[NO REG]],S8),"")
5.=IFERROR(INDEX(TblDataBuyer,MATCH([@[NAMA PEMBELI]],TblDataBuyer[BUYER],0),4),"DATABASE KOSONG!!")
6. =IFERROR(INDEX(TblDataBuyer,MATCH([@[NAMA PEMBELI]],TblDataBuyer[BUYER],0),5),"DATABASE KOSONG!!")
7.=IF([@[JENIS TAGIHAN]]="PPN",([@QTY]*[@[HARGA JUAL]])+([@QTY]*[@[HARGA JUAL]]*0.1),IF([@[JENIS TAGIHAN]]="KBN",[@QTY]*[@[HARGA JUAL]],[@QTY]*[@[HARGA JUAL]]))


I am try to make VBA formula 1st, but that just works on one row..

Sub ID_Item_No_PO_Column()
If Range("E8") <> "" Then
Range("A8").Select
ActiveCell.FormulaR1C1 = Range("E8") & "-" & Range("F8")
Else
Range("A8").Select
ActiveCell.FormulaR1C1 = ""
End If
End Sub

 

[mole999]

You might build a loop

Sub ID_Item_No_PO_Column()

Dim n as INTEGER
for N = 8 to 50 step 1
If Range("E"&n) <> "" Then
Range("A"& n).Select
ActiveCell.FormulaR1C1 = Range("E"&n) & "-" & Range("F"&n)
Else
Range("A"&n).Select
ActiveCell.FormulaR1C1 = ""
End If
next n
End Sub

 

[afahruf90]

This Macro worked.. But If I change Range E.. I must re-run macro ID_Item_No_PO_Column..
I hope somebody can help me for change to be automatically macro..

 

[afahruf90]

1. I am still learning how to write formula excel =IFERROR(INDEX(TblDataBuyer,MATCH([@[NAMA PEMBELI]],TblDataBuyer[BUYER],0),4),"DATABASE KOSONG!!") on macro. I was tried write this code :

Sub Marketing_Column()
Dim n As Integer
For n = 8 To 20 Step 1
Range("H" & n).Select
ActiveCell.FormulaR1C1 = expression.IfError(expression.Index(Worksheets("Database").ListObjects("TblDataBuyer"), expression.Match(Range("G" & n), Worksheets("Database").ListObjects("TblDataBuyer").ListColumns("Buyer").DataBodyRange.Select, 0), 4), "Database Kosong")
Next n
End Sub

And the result is.. Run-Time Error '1004' .. Where is my false?


2. Macro can't re-calculate automatic when I change value cell..

You might build a loop

Sub ID_Item_No_PO_Column()

Dim n as INTEGER
for N = 8 to 50 step 1
If Range("E"&n) <> "" Then
Range("A"& n).Select
ActiveCell.FormulaR1C1 = Range("E"&n) & "-" & Range("F"&n)
Else
Range("A"&n).Select
ActiveCell.FormulaR1C1 = ""
End If
next n
End Sub