Jump to content

[SOLVED] How to update an array after storing an Excel Column then changing it.


nooneclose
 Share

Recommended Posts

Hey. I'm working on a new project and was wondering if there is a better way to "update" my Column E array. 

Here is my code: 

Local $nI  = 0                                                            ;Creates a name index of 0: nI = Name index
Local $nII = 1                                                            ;Creates a name index of 1 for second loop: nII = Name Index 2

For    $iN = 0 To $IndexRows Step 1                                       ;Checks the roster for any names that appear twice
     For $iN2 = 0 To $IndexRows Step 1
         if $d_Names[$nI] == $d_Names[$nII] And $d_Names[$nII] <> "" Then

             Local $timeSheetName = _ArraySearch($e_Names, $d_Names[$nI], 0, 0, 0, 0, 1)
             ;MsgBox($MB_SYSTEMMODAL, "Found it", $d_Names[$nI] & " In column E on Row " & $timeSheetName)

             Local $eI  = $timeSheetName + 1

             ;ConsoleWrite($timeSheetName & @CRLF)
             ;ConsoleWrite($eI & @CRLF)
             ;ConsoleWrite(@CRLF)


             _Excel_RangeInsert($OpenWorkbook.ActiveSheet, "E" & $eI & ":F" & $eI, $xlShiftDown)
                                                                          ;Inserts a empty cell in columns E and F.
             _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $d_Names[$nII], "E" & $eI)
                                                                          ;Fills the empty cell in columns E with the doubled name

             $aArray_Index = 2                                            ;Array element counter

             For $Index = 2 To $IndexRows Step 1                          ;Loops through every row in the Excel file unto no rows are found or a null row is found
                 $Array_Value_E = _Excel_RangeRead($OpenWorkbook, Default, "E"&$Index)
                 $e_names[$aArray_Index] = $Array_Value_E                 ;While the code loops every value in column E is stored in the E array (updating the array)

                 $aArray_Index += 1
             Next

             ExitLoop
         EndIf

     Next

     $nI  += 1
     $nII += 1
Next

Basically, It checks a roster for people whose name appears twice then inserts a new "row" for that person because they work in two different departments.

I have to find that name however in Column E if two appear in column D. My code works but I think it is not as efficient as it could be. 

Any ideas on how to improve the "update" for my array?

(once it finds the double names in Column D it then searches for that name by going name by name in the Column E array and once it finds it inserts a new row. However, the E array doesn't have that new row stored in it so I have to "update" the array to properly find the next name)

Any and all tips would be greatly appreciated. 

 

NOTE: Just assume I'm opening the excel file properly please do not add that code in, it only complicates your answer. 

Edited by nooneclose
Link to comment
Share on other sites

Do not use _Excel_RangeRead to loop through column E, use _Excel_RangeFind and search column E for the name.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

More information please. What is the return value and the value of @error and @extended after calling _Excel_RangeFind?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

@water  I was able to figure it out on my own. thank you again for guiding me to the proper function.

This is what I'm using and it works at least 20 times faster.  (my code basically runs instantly now)

For $iN = 0 To $IndexRows Step 1                                        ;Checks the roster for any names that appear twice
     For $iN2 = 0 To $IndexRows Step 1
         if $d_Names[$nI] == $d_Names[$nII] And $d_Names[$nII] <> "" Then
             Local $timeSheetName = _Excel_RangeFind($OpenWorkbook, $d_Names[$nII], "E1:E" & $IndexRows)
             Local $eI = $timeSheetName[0][2]
             Local $Cell_Number = StringSplit($eI, "A, B, C, D, E, F, G, H, $")
             Local $refined_number = $Cell_Number[4] + 1
            
             _Excel_RangeInsert($OpenWorkbook.ActiveSheet, "E" & $refined_number & ":F" & $refined_number, $xlShiftDown)
                                                                        ;Inserts a empty cell in columns E and F.
             _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $d_Names[$nII], "E" & $refined_number)
                                                                        ;Fills the empty cell in columns E with the doubled name
             ExitLoop
         EndIf
     Next
     $nI  += 1
     $nII += 1
Next

 

Link to comment
Share on other sites

Glad you got it working ;)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share

×
×
  • Create New...