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

[nooneclose]

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: 

expandcollapse popup

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 November 1, 2018 by nooneclose

[water]

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

[nooneclose]

@water Thank you! I will try that and get back with you. 

[nooneclose]

@water I tried using 

$Array_Value_E = _Excel_RangeFind($OpenWorkbook, $d_Names[$nII], "E1:E176")

but I can't seem to get this function to work. It doesn't find the value that I'm looking for. 

[water]

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

[nooneclose]

@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

 

[water]

Glad you got it working