don00 Posted February 9, 2021 Share Posted February 9, 2021 Hello; I'm new member in the community, and i appreciate your Assistance. I have a small Excel project, which i need to modify, the following script is working fine but i just wanted to add some functions into the same loop if possible. The main function for the whole script is, to open target Excel file, create 3 new work sheets, and color them, (so at this point there will be 4 total work sheets, one is the (Main) which includes the data, and 3 new once to be used later on. then Store the numbers of the last row and column of the default work sheet. then split column (B) in this worksheet so that only date would be visible. till here all is done in the script. i need to add the following: A) to include in the same (for) loop which already exist in the script, sort the dates in a way that newest items on top (for the main sheet), b) to find data items, where column (A) include work of "Asap", then copy the found rows to the second worksheet (TargetRed), highlight only the row with Red color in the (main) worksheet c) to fined the rows where there is "DTA" in column "A", and empty space in Column "F". copy the found rows to (TargetGreen), also highlight this row in green, and put comment "ok" somewhere in the end of the row (in TargetGreen ) c) if a row doesn't satisfy conditions from step (b) and (C), then highlight the row in orange color, copy it to (TargetOrange). D) show message box to user says: "number of green items:..." "number of Red items:..." "number of Orange items:..." #include <Excel.au3> Local $_oExcel1 = _Excel_Open() Local $sFilePath = "C:\Users\Desktop\Files\project.xlsx" Local $_oWorkbook = _Excel_BookOpen($_oExcel1, $sFilePath) _Excel_SheetAdd($_oWorkbook, Default, False, 3, "Target Green|Target Orange|Target Red") Local $oWsTargetGreen = $_oWorkbook.Worksheets("Target Green") $oWsTargetGreen.Tab.ColorIndex = 10 Local $oWsTargetOrange = $_oWorkbook.Worksheets("Target Orange") $oWsTargetOrange.Tab.ColorIndex = 45 Local $oWsTargetRed = $_oWorkbook.Worksheets("Target Red") $oWsTargetRed.Tab.ColorIndex = 3 Local $oWsSource = $_oWorkbook.Worksheets("Source") Local $iLastRow = $oWsSource.Range("A1").SpecialCells($xlCellTypeLastCell).Row Local $LastiColumn = $oWsSource.Range("A1").SpecialCells($xlCellTypeLastCell).Column MsgBox(0, "The number of Rows", $iLastRow) MsgBox(0, "The number of Columns", $LastiColumn) for $i = 2 to $iLastRow Local $sTransactionDateTime = $oWsSource.Range("B" & $i).value Local $aTransactionDateTime = StringSplit($sTransactionDateTime, " ", 2) $oWsSource.Range("B" & $i).value = $aTransactionDateTime[0] Next Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted February 9, 2021 Moderators Share Posted February 9, 2021 Moved to the appropriate forum. Moderation Team Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area Link to comment Share on other sites More sharing options...
Nine Posted February 9, 2021 Share Posted February 9, 2021 (edited) I think the easiest way would be to load all the source tab data into a 2D array. Then you can perform all the tasks you want using _ArraySort, etc. When ready, write those infos to each Excel tab. When posting code, please this tool. Edited February 9, 2021 by Nine “They did not know it was impossible, so they did it” ― Mark Twain Spoiler Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search in text (au3) files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector Printer Manager GIF Animation (cached) Screen Scraping Multi-Threading Made Easy Link to comment Share on other sites More sharing options...
water Posted February 9, 2021 Share Posted February 9, 2021 .A ... sort the dates ... Use function _Excel_RangeSort .B ... find "Asap" ... Use function _Excel_RangeFind. Use function _Excel_RangeCopyPaste to copy the data to another sheet. .C ... find "DTA" ... Use function _Excel_RangeFind. Column 2 of the returned array holds the address of the cell. use .offset to get the value of column B and check for empty 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...
junkew Posted February 10, 2021 Share Posted February 10, 2021 Why would you not write it in VBA macro? D 😉 #include <MsgBoxConstants.au3> msgbox($MB_SYSTEMMODAL, "Title","number of green items:...") msgbox($MB_SYSTEMMODAL, "Title","number of Red items:...") msgbox($MB_SYSTEMMODAL, "Title","number of Orange items:...") FAQ 31 How to click some elements, FAQ 40 Test automation with AutoIt, Multithreading CLR .NET Powershell CMDLets Link to comment Share on other sites More sharing options...
don00 Posted February 10, 2021 Author Share Posted February 10, 2021 doing it in VBA is easy, just the project i work in it requires Autoit Link to comment Share on other sites More sharing options...
junkew Posted February 10, 2021 Share Posted February 10, 2021 It helps if you make it in VBA and partly in AutoIt and post both pieces of coding as then people can help you easier in translating the magic numbers or the less obvious sometimes in AutoIt to deal with ranges instead of iterating line by line FAQ 31 How to click some elements, FAQ 40 Test automation with AutoIt, Multithreading CLR .NET Powershell CMDLets Link to comment Share on other sites More sharing options...
don00 Posted February 11, 2021 Author Share Posted February 11, 2021 Hello; I would appreciate if you could assist, the idea is that i need to run function on the (source) sheet, to check all column A for the string "SLS", if that happened, then just copy this row into the (target red) sheet, and also Mark this row red in the (source file). i just need here inside the (For) Loop, i did assigned the Column A as dynamic variable (since it will need to include the whole column), and then i need to run inside this cycle conditional statement , >> "if value of range A1 includes "SLS" (of the source sheet) >> then >>copy the found rows to worksheet (Target Red), and also highlight the row with red color in the worksheet (source). am not sure how to correctly adjust the IF statement inside the loop. to give the required result at the end. expandcollapse popup#include <Excel.au3> Local $sFilePath = "\\Disktop\Files\project.xlsx" Local $_oExcel1 = _Excel_Open() Local $_oWorkbook = _Excel_BookOpen($_oExcel1, $sFilePath) _Excel_SheetAdd($_oWorkbook, Default, False, 3, "Target Green|Target Orange|Target Red") Local $oWsTargetGreen = $_oWorkbook.Worksheets("Target Green") $oWsTargetGreen.Tab.ColorIndex = 10 Local $oWsTargetOrange = $_oWorkbook.Worksheets("Target Orange") $oWsTargetOrange.Tab.ColorIndex = 45 Local $oWsTargetRed = $_oWorkbook.Worksheets("Target Red") $oWsTargetRed.Tab.ColorIndex = 3 Local $oWsSource = $_oWorkbook.Worksheets("Source") Local $iLastRow = $oWsSource.Range("A1").SpecialCells($xlCellTypeLastCell).Row Local $iLastColumn = $oWsSource.Range("A1").SpecialCells($xlCellTypeLastCell).Column Local $sLastColumn = _Excel_ColumnToLetter($iLastColumn) MsgBox(0, "The number of Rows", $iLastRow) MsgBox(0, "The number of Columns", $iLastColumn) For $i = 2 To $iLastRow Local $sTransactionDateTime = $oWsSource.Range("B" & $i).value Local $aTransactionDateTime = StringSplit($sTransactionDateTime, " ", 2) $oWsSource.Range("B" & $i).value = $aTransactionDateTime[0] Local $ColumnA = $i Local $read1 = _Excel_RangeRead($_oWorkbook, $oWsSource, "A" & $i, 3) StringInStr("SLS", 0) If $read1 = "SLS" Then _Excel_RangeWrite($_oWorkbook, $oWsTargetRed, $read1, "A" & $i) EndIf Next Link to comment Share on other sites More sharing options...
don00 Posted February 11, 2021 Author Share Posted February 11, 2021 sorry for typing mistake.. here is what the if function supposed to do : >> "if value of range A1 till the last row....includes "SLS" (of the source sheet) >> then >>copy the found rows to worksheet (Target Red), and also highlight the row with red color in the worksheet (source). Link to comment Share on other sites More sharing options...
Nine Posted February 11, 2021 Share Posted February 11, 2021 Try this loop : Local $aRow, $iOut = 0 For $i = 2 To $iLastRow If StringInStr($oWsSource.Range("A" & $i).Value, "SLS", $STR_CASESENSE) Then $iOut += 1 $aRow = _Excel_RangeRead($_oWorkbook, $oWsSource, "A" & $i & ":" & $sLastColumn & $i) _Excel_RangeWrite($_oWorkbook, $oWsTargetRed, $aRow, "A" & $iOut) $oWsSource.Range("A" & $i & ":" & $sLastColumn & $i).Interior.ColorIndex = 3 EndIf Next “They did not know it was impossible, so they did it” ― Mark Twain Spoiler Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search in text (au3) files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector Printer Manager GIF Animation (cached) Screen Scraping Multi-Threading Made Easy Link to comment Share on other sites More sharing options...
don00 Posted February 11, 2021 Author Share Posted February 11, 2021 Thanks alot @Nine , thats exactly what i need. Only small issue, it does copy the mentioned row from the sheet (source) and paste it to the (Target Red )sheet. the problem is that it paste it in a different format. since there are dates and numbers, is there anyway to arrange it so that i could copy and paste exactly same format as it was originally in the source? here is screenshot of the (source) screenshot: Link to comment Share on other sites More sharing options...
Nine Posted February 11, 2021 Share Posted February 11, 2021 Try with : _Excel_RangeCopyPaste Instead of read/write. “They did not know it was impossible, so they did it” ― Mark Twain Spoiler Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search in text (au3) files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector Printer Manager GIF Animation (cached) Screen Scraping Multi-Threading Made Easy Link to comment Share on other sites More sharing options...
don00 Posted February 11, 2021 Author Share Posted February 11, 2021 i tried to combine both source and target in same line. but nothing happens. neither copy not paste. please advise what might be wrong in it. also am sure that i wrote more lines than what is actually needed. i just try all possible scenarios. If StringInStr($oWsSource.Range("A" & $i).Value, "SLS", $STR_CASESENSE) Then $iOut += 1 $aRow = _Excel_RangeRead($_oWorkbook, $oWsSource, "A" & $i & ":" & $sLastColumn & $i) Local $RangeSource = $oWsSource.range("A" & $i & ":" & $sLastColumn & $i) Local $RangeTarget = $oWsTargetRed.range("A" & $iOut) _Excel_RangeCopyPaste($_oWorkbook, $RangeSource, $RangeTarget) $oWsSource.Range("A" & $i & ":" & $sLastColumn & $i).Interior.ColorIndex = 3 EndIf Link to comment Share on other sites More sharing options...
Nine Posted February 11, 2021 Share Posted February 11, 2021 (edited) 1 hour ago, don00 said: i tried to combine both source and target in same line You can't. You need two different statements. See examples in help file... Edited February 11, 2021 by Nine “They did not know it was impossible, so they did it” ― Mark Twain Spoiler Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search in text (au3) files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector Printer Manager GIF Animation (cached) Screen Scraping Multi-Threading Made Easy Link to comment Share on other sites More sharing options...
Nine Posted February 11, 2021 Share Posted February 11, 2021 (edited) That is false. You can do it in a single line. I always used it with 2, but it is feasible to do it in a single line. Just tested it...Sorry for confusion. Edited February 11, 2021 by Nine “They did not know it was impossible, so they did it” ― Mark Twain Spoiler Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search in text (au3) files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector Printer Manager GIF Animation (cached) Screen Scraping Multi-Threading Made Easy Link to comment Share on other sites More sharing options...
don00 Posted February 11, 2021 Author Share Posted February 11, 2021 yes i have tested it now in a single line, its just there is no data get pasted, always empty. not sure if the range data what i put is wrong or what could be the issue. if you have tested it kindly share me to see what was my mistake. Link to comment Share on other sites More sharing options...
Nine Posted February 11, 2021 Share Posted February 11, 2021 Here : Local $oRange, $iOut = 0 For $i = 2 To $iLastRow If StringInStr($oWsSource.Range("A" & $i).Value, "SLS", $STR_CASESENSE) Then $iOut += 1 $oRange = $oWsTargetRed.Range("A" & $iOut & ":" & $sLastColumn & $iOut) _Excel_RangeCopyPaste($oWsSource, "A" & $i & ":" & $sLastColumn & $i, $oRange, False, $xlPasteAll) $oWsSource.Range("A" & $i & ":" & $sLastColumn & $i).Interior.ColorIndex = 3 EndIf Next “They did not know it was impossible, so they did it” ― Mark Twain Spoiler Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search in text (au3) files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector Printer Manager GIF Animation (cached) Screen Scraping Multi-Threading Made Easy Link to comment Share on other sites More sharing options...
don00 Posted February 11, 2021 Author Share Posted February 11, 2021 Here is the full script from my end, shows error, please advise. expandcollapse popup#include <Excel.au3> Local $sFilePath = "C:\Users\ahmed\OneDrive\Desktop\training\Assignment 1.xlsx" Local $_oExcel1 = _Excel_Open() Local $_oWorkbook = _Excel_BookOpen($_oExcel1, $sFilePath) _Excel_SheetAdd($_oWorkbook, Default, False, 3, "Target Green|Target Orange|Target Red") Local $oWsTargetGreen = $_oWorkbook.Worksheets("Target Green") $oWsTargetGreen.Tab.ColorIndex = 10 Local $oWsTargetOrange = $_oWorkbook.Worksheets("Target Orange") $oWsTargetOrange.Tab.ColorIndex = 45 Local $oWsTargetRed = $_oWorkbook.Worksheets("Target Red") $oWsTargetRed.Tab.ColorIndex = 3 Local $oWsSource = $_oWorkbook.Worksheets("Source") Local $iLastRow = $oWsSource.Range("A1").SpecialCells($xlCellTypeLastCell).Row Local $LastiColumn = $oWsSource.Range("A1").SpecialCells($xlCellTypeLastCell).Column MsgBox(0, "The number of Rows", $iLastRow) MsgBox(0, "The number of Columns", $LastiColumn) Local $oRange, $iOut = 0 For $i = 2 To $iLastRow Local $sTransactionDateTime = $oWsSource.Range("B" & $i).value Local $aTransactionDateTime = StringSplit($sTransactionDateTime, " ", 2) $oWsSource.Range("B" & $i).value = $aTransactionDateTime[0] If StringInStr($oWsSource.Range("A" & $i).Value, "SLS", $STR_CASESENSE) Then $iOut += 1 $oRange = $oWsTargetRed.Range("A" & $iOut & ":" & $LastiColumn & $iOut) _Excel_RangeCopyPaste($oWsSource, "A" & $i & ":" & $LastiColumn & $i, $oRange, False, $xlPasteAll) $oWsSource.Range("A" & $i & ":" & $LastiColumn & $i).Interior.ColorIndex = 3 EndIf Next Link to comment Share on other sites More sharing options...
Nine Posted February 11, 2021 Share Posted February 11, 2021 You need to use the string $sLastColumn not the numeric value. “They did not know it was impossible, so they did it” ― Mark Twain Spoiler Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search in text (au3) files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector Printer Manager GIF Animation (cached) Screen Scraping Multi-Threading Made Easy Link to comment Share on other sites More sharing options...
don00 Posted February 11, 2021 Author Share Posted February 11, 2021 got it, all is ok now, i appreciate your support. 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