tetsajin Posted March 6, 2018 Share Posted March 6, 2018 Hi, I'm fairly new to auto it and I was wondering if there was a way to automate writing a formula into excel but have the cell number change depending on the list. What I want to do is, grab a table from a website and then filter that data for a particular key word. From there, I would input the formula "=LEFT(H1,FIND("/",H1)-1)" to find the string left of the character "/". In Excel I would just drag down the cell and it'll move on to the H2 cell and so on... I can't seem to get it to work within autoit. I'm also unsure how to make autoit know when to end the "drag down". For now, I thought hardcoding the maximum the data can become would work... Any help would be appreciated. Link to comment Share on other sites More sharing options...
Juvigy Posted March 6, 2018 Share Posted March 6, 2018 It can be done. Show us what you have so far. Please use 'code tags'. Link to comment Share on other sites More sharing options...
Subz Posted March 6, 2018 Share Posted March 6, 2018 Example: #include <Excel.au3> ;~ Example.xlsx Spreadsheet layout ;~ A | B | C ;~ 1 | 9 | ;~ 2 | 8 | ;~ 3 | 7 | ;~ 4 | 6 | ;~ 5 | 5 | ;~ 6 | 4 | ;~ 7 | 3 | ;~ 8 | 2 | ;~ 9 | 1 | Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Example.xlsx") ;~ Write Formula to C1 = A1 + B1 _Excel_RangeWrite($oWorkbook, Default, "=A1+B1", "C1", False) ;~ Copy and Paste Formula from C1 to C2:C9 _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, "C1", "C2:C9", False, $xlPasteFormulas) Link to comment Share on other sites More sharing options...
tetsajin Posted March 6, 2018 Author Share Posted March 6, 2018 Local $arr[50] For $i = 0 to 50 Step + $arr[$i] = "=LEFT(H" $i ",FIND("/",H" $i ")-1)" Next Local $arr[50] $arr[0] = "=LEFT(H1,FIND("/",H1)-1)" Thanks for answering so quick! I really only have tried two different ways... For the first one, I tried to do a loop but I get an error, "Badly formatted variable or macro" and I read somewhere that you can't use a for loop to modify arrays so I gave up on that approach... I also tried to hardcode it but when I do that, my array displays as -1.#IND so I'm unsure where that went wrong. Link to comment Share on other sites More sharing options...
water Posted March 6, 2018 Share Posted March 6, 2018 (edited) I think it should be (untested): $arr[$i] = '=LEFT(H' & $i & ',FIND("/",H' & $i & ')-1)' Edit: Comma ist correct, semicolon is a language dependant setting. Edited March 6, 2018 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Subz Posted March 6, 2018 Share Posted March 6, 2018 As Water mentioned use single quotes around double quotes and also need to use $i = 1 To 50 example: #include <Array.au3> Local $arr[50] $arr[0] = UBound($arr) - 1 For $i = 1 To UBound($arr) - 1 $arr[$i] = '=LEFT(H' & $i & ',FIND("/",H' & $i & ')-1)' Next _ArrayDisplay($arr) Link to comment Share on other sites More sharing options...
water Posted March 6, 2018 Share Posted March 6, 2018 (edited) Shouldn't there be a ; (instead of ,) as parameter separator in Excel functions? Edit: Comma ist correct, semicolon is a language dependant setting. Edited March 6, 2018 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Subz Posted March 6, 2018 Share Posted March 6, 2018 (edited) Not that I'm aware of, (,) is separator in Excel Left function i.e. Left(text, [num chars]) Edited March 6, 2018 by Subz Link to comment Share on other sites More sharing options...
water Posted March 6, 2018 Share Posted March 6, 2018 I'm running Office 2017 on a german system and the function only works with ";" - so it seems language dependant. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Subz Posted March 6, 2018 Share Posted March 6, 2018 Hi Water, it is a regional setting, I believe, if you look at List separator you'll probably find your seeing a semi-colon, which should mean your csv files are being separated by semi-colon rather than comma as well. Link to comment Share on other sites More sharing options...
water Posted March 6, 2018 Share Posted March 6, 2018 Correct That's an OS setting. I have updated my post accordingly. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
tetsajin Posted March 7, 2018 Author Share Posted March 7, 2018 Thank you guys so much! It worked like a charm 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