Sign in to follow this  
Followers 0
Kaworu

Loop for missing data and data entry form

5 posts in this topic

#1 ·  Posted

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.

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by 232showtime

ill get to that... i still need to learn and understand a lot of codes graduated.gif

Correct answer, learn to walk before you take on that marathon.

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by Kaworu

Share this post


Link to post
Share on other sites

#4 ·  Posted

what is the exact error you got??


ill get to that... i still need to learn and understand a lot of codes graduated.gif

Correct answer, learn to walk before you take on that marathon.

Share this post


Link to post
Share on other sites

#5 ·  Posted

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. 

Share this post


Link to post
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
Sign in to follow this  
Followers 0