Jump to content
Sign in to follow this  
CC_Mu

Excel insert function Divide

Recommended Posts

CC_Mu

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 this post


Link to post
Share on other sites
CC_Mu

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

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

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×