CC_Mu Posted May 1, 2018 Share Posted May 1, 2018 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 expandcollapse popup;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;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 Link to comment Share on other sites More sharing options...
CC_Mu Posted May 1, 2018 Author Share Posted May 1, 2018 ok, I found out later by myself, the code should be Local $Formula2 ="="&$GP2&"/"&$Unit2 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now