Jump to content
Sign in to follow this  
autorobot

copying formula to multiple cells

Recommended Posts

autorobot

I'm new to using autoit, but I would like to use it with excel. I'm trying to open an excel file, insert and name that column in (1,1). Then in row 2, column 1, I need to concatenate (C2 and K2). Up to this point I'm okay. How do I propagate the concatenate to row3, 1, row4, 1, row5, 1 and so on until the end of the spreadsheet? Here is my code:

#include <Excel.au3>

DriveMapAdd ("Z:", "\")

$FilePath="Z:\SalesOrders_and_BackOrders.xls"

$oExcel = _ExcelBookOpen($FilePath)

If @error = 1 Then

MsgBox(0, "Error!", "Unable to Create the Excel Object")

Exit

ElseIf @error = 2 Then

MsgBox(0, "Error!", "File does not exist - Shame on you!")

Exit

EndIf

_ExcelColumnInsert($oExcel, 1, 1) ;Inserting Unique ID Column

_ExcelWriteCell($oExcel, "Unique ID", 1, 1) ;Labeling as Unique ID

_ExcelWriteCell($oExcel, "=CONCATENATE(C2,K2)", 2, 1) ;Concatenate Build and Stock Code Columns

So this is where I stand, I don't know how to increment the row # if I were going to use a For loop or do..until. Thanks in advance for the assistance.

Share this post


Link to post
Share on other sites
JoHanatCent

Try:

#include <Excel.au3>
;DriveMapAdd ("Z:", "\")
$FilePath = "Z:\SalesOrders_and_BackOrders.xls"
$oExcel = _ExcelBookOpen($FilePath )
If @error = 1 Then
MsgBox(0, "Error!", "Unable to Create the Excel Object")
Exit
ElseIf @error = 2 Then
MsgBox(0, "Error!", "File does not exist - Shame on you!")
Exit
EndIf
Global $iLastRow = $oExcel.Cells.SpecialCells($xlCellTypeLastCell).Row
_ExcelColumnInsert($oExcel, 1, 1) ;Inserting Unique ID Column
_ExcelWriteCell($oExcel, "Unique ID", 1, 1) ;Labeling as Unique ID
For $i = 2 To $iLastRow
_ExcelWriteFormula($oExcel, "=CONCATENATE(R" & $i & "C3,R" & $i & "C11)", $i, 1)
Next
; _ExcelWriteCell($oExcel, "=CONCATENATE(C2,K2)", 2, 1) ;Concatenate Build and Stock Code Columns

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  

×