Kaworu Posted April 16, 2017 Share Posted April 16, 2017 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. Link to comment Share on other sites More sharing options...
232showtime Posted April 16, 2017 Share Posted April 16, 2017 (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 April 16, 2017 by 232showtime ill get to that... i still need to learn and understand a lot of codes Correct answer, learn to walk before you take on that marathon. Link to comment Share on other sites More sharing options...
Kaworu Posted April 16, 2017 Author Share Posted April 16, 2017 (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 April 16, 2017 by Kaworu Link to comment Share on other sites More sharing options...
232showtime Posted April 16, 2017 Share Posted April 16, 2017 what is the exact error you got?? ill get to that... i still need to learn and understand a lot of codes Correct answer, learn to walk before you take on that marathon. Link to comment Share on other sites More sharing options...
Kaworu Posted April 16, 2017 Author Share Posted April 16, 2017 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. 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