# Excel insert function Divide

## Recommended Posts

what I want to do is to add a new column by sum Column A and Column B, and then add another new column to divide Column A by Column B if Column B is not zero, and then copy the formula to the last row by changing the row number (e.g. row 1 is  A1+B1, then row 2 will be A2+B2).

I am able to do sum, but can not divide, any insight here? thank you!

the error msg say \$GP2 and \$Unit2 is successfully designated to the right variable (AE2 and AI2), but the following divide can not make the formula to be AE2/AI2, how can I do the divide?

`Local \$Formula2 =\$GP2/\$Unit2`
```;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;Full code is here
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;Sum part is good to go
; Add 9 new column to sum by product
Local \$Name[9] = ["name1", "name2", "name3", "name4", "name5", "name6", "name7" ,"name8", "name9"]
Local \$Col[9] = ["AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM"]
Local \$Formula[9] = ["=B2+C2+D2", "=E2+F2+G2", "=H2+I2+J2", "=K2+L2+M2", "=N2+O2+P2", "=Q2+R2+S2", "=T2+U2+V2", "=Y2+Z2+AA2", "=AB2+AC2+AD2"]

For \$i = 0 To UBound(\$Name)-1
ConsoleWrite("Testing " & \$Name[\$i] & \$Col[\$i] & \$Formula[\$i] & @CRLF)

_Excel_RangeWrite(\$oWorkbook, \$oWorkbook.ActiveSheet, \$Name[\$i], \$Col[\$i]&"1")              ;Add New Column Name
_Excel_RangeWrite(\$oWorkbook, \$oWorkbook.ActiveSheet, \$Formula[\$i], \$Col[\$i]&"2", False)    ;Add Column Formula
_Excel_RangeCopyPaste(\$oWorkbook.ActiveSheet, \$Col[\$i]&"2", \$Col[\$i]&"3:"&\$Col[\$i]&\$LastRow, Default, \$xlPasteFormats)   ;Copy Formula to the rest of Column

Next

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;Divide part failed
; Add column to get Gross Profit per Unit
Local \$Name[2] = ["name10", "name11"]
Local \$Col[2] = ["AN", "AO"]
Local \$GP[2] = ["AE", "AF"] ;["name1", "name2"]
Local \$Unit[2] = ["AI", "AJ"];["name5", "name6"]
;Local \$Formula = ["=AE2/AI2", "=AF2/AJ2"]

For \$i = 0 To UBound(\$Name)-1
Local \$GP2 = \$GP[\$i]&"2"
Local \$Unit2 = \$Unit[\$i]&"2"
Local \$Formula2 =\$GP2/\$Unit2
MsgBox(1,"", \$GP2)
MsgBox(1,"", \$Unit2)
MsgBox(1,"", \$Formula2)
_Excel_RangeWrite(\$oWorkbook, \$oWorkbook.ActiveSheet, \$Name[\$i], \$Col[\$i]&"1")                      ;Add New Column Name
;If \$Unit[\$i] = 0 Then                                                                              ;Add New Column Formula
;   _Excel_RangeWrite(\$oWorkbook, \$oWorkbook.ActiveSheet, "0", \$Col[\$i]&"2:"&\$Col[\$i]&\$LastRow)
;Else
_Excel_RangeWrite(\$oWorkbook, \$oWorkbook.ActiveSheet, \$Formula2, \$Col[\$i]&"2", False)
_Excel_RangeCopyPaste(\$oWorkbook.ActiveSheet, \$Col[\$i]&"2", \$Col[\$i]&"3:"&\$Col[\$i]&\$LastRow, Default, \$xlPasteFormats)
;EndIf
Next```

##### Share on other sites

ok, I found out later by myself, the code should be

`Local \$Formula2 ="="&\$GP2&"/"&\$Unit2`

## Create an account

Register a new account

• ### Recently Browsing   0 members

×

• Wiki

• Back

• #### Beta

• Git
• FAQ
• Our Picks
×
• Create New...