Loop for missing data and data entry form

[Kaworu]

Hello esteemed members of this forum,

I'm writing little script/program to automate few things at work. I'm having problem(don't know how to realise my idea) with one part of the code . Let’s start with what it does, $oWorkbook1 is excel file coming from planning program and is loaded with FileOpenDialog, replaces few things then removes one column, if statement to make sure it doesn't remove it on second run. Now we are getting to what I'm stuck on; For loop, it does what is in the script i.e. checks for empty cells in column L and shows value of column D of that corresponding cell. ***What I need it to do is pop small gui window containing label with value of $acrange[$j][0] and input box where I can write what’s missing, then press next button to go to next missing entry. Position of the missing values change and even value change on daily basis so I can't hard code it. This bit of code is a Case in main While loop, correct or not it works, I'm not super worried about code optimisation for now, I just want to get it to work as let’s say final product and then I will take time to clean it up. I can't really post the rest of the code, it does not impact this function of the program and I would have to heavily edit it for public viewing

Any help or solution is appreciated.

Absolute killer would be to have one more window that would list all $acrange[$j][0] for missing values press ready button and then go to ***

Case $StartNightShift
            $oExcel = _Excel_Open()
            $oWorkbook2 = _Excel_BookOpen($oExcel, $loadedFile, False)
            $lrow_source = $oWorkbook2.Worksheets(1).Range("A1").End(-4121).Row
            _Excel_RangeReplace($oWorkbook2, Default, Default, "32N", "32A", $xlWhole)
            _Excel_RangeReplace($oWorkbook2, Default, Default, "32M", "32C", $xlWhole)
            $shiftcol = _Excel_RangeRead($oWorkbook2, Default, $oWorkbook2.Worksheets(1).Range("E1"))
            If $shiftcol = "Shift" Then
            _Excel_RangeDelete($oWorkbook2.Worksheets(1), "E:E", Default, 2)
            EndIf
            $acrange = _Excel_RangeRead($oWorkbook2, Default, $oWorkbook2.Activesheet.Range("D2:L" & $lrow_source))
            For $j = 0 To UBound($acrange, 1) - 1
                If $acrange[$j][8] = 0 Then
                    MsgBox($MB_SYSTEMMODAL, "", "Flight number: " & $acrange[$j][0] & ", AC type: " & $acrange[$j][8] & "     " & $j)
            Next
            _Excel_RangeSort($oWorkbook2, Default, "A2:R" & $lrow_source, "I:I", Default, $xlSortTextAsNumbers, $xlNo, False, Default, "M:M", Default, "H:H", Default)
            _Excel_RangeCopyPaste($oWorkbook2.Worksheets(1), "A2:Q" & $lrow_source)
            $oWorkbook1 = _Excel_BookAttach($sWorkbook1, "FileName")
            _Excel_RangeCopyPaste($oWorkbook1.Worksheets("IFR Report"), Default, "A4", False, $xlPasteValues)
            $lrow_target = $oWorkbook1.Worksheets("IFR Report").Range("A4").End(-4121).Row
            _Excel_RangeSort($oWorkbook1, "IFR Report", "A4:U" & $lrow_target, "S:S", Default, $xlSortTextAsNumbers, $xlNo, False, Default, "M:M", Default, "H:H", Default)

 

Thank you very much for taking your time to read this post. 

 

PS. Not sure if title is correct to want I need.

[232showtime]

its better if you show a working script, can you show the _arraydisplay($acrange) list?

 

also  you missed to close the if statement in your for loop.   nvm this just my mistake

 

Edited April 16, 2017 by 232showtime

[Kaworu]

Hey,

Unfortunately I can't show any data. The whole array hold only two columns that are of interest to me, column D number 0 and column L number 8. Lets say column D hold data is this format

Column D           Columns L

AB1234               32x

AB2345               

AB3456               32z

 

Yeah I noticed that missing EndIf, i was removed my failed attempt of trying to write it.

Edited April 16, 2017 by Kaworu

[232showtime]

what is the exact error you got??

[Kaworu]

There is no really any error I just don't know how to materialise this " What I need it to do is pop small gui window containing label with value of $acrange[$j][0] and input box where I can write what’s missing, then press next button to go to next missing entry. " instead of the MsgBox.