I can have a look, given that it's not urgent
Weekly We Receive Data Via Whatsapp, Emails & Hardcopy List in order to Replace the Results & the Day it was Executed.
Let's Say there are 980 Items in total in one Category and on a random day around 27 was Conducted with a Test.
we Have to Access the main File (DTL3 Trains TR2) and Replace them Manually. It is Extremely Time Consuming Because we have to Manually Look up for the Category and Overwrite the Results & Date Executed.
So i created a Test File Called (ZQ File), Instead of Manually Searching & Overwriting, we type in the Following Requirements such as Alarm ID, Train & Alarm Category Along with their Corresponding Results & Date Executed.
In the ZQ File, There are Four Green Tabs Highlighted which are the Category for the Alarms , i need a Macro which could Categorize & Match The Three Criteria Above & Overwrite the corresponding Date & Results From ZQ File to DTL3 Trains Files. Please Note that the ZQ File is to be Updated, so the macro has to be able to Lookup Newly Inputted Data & Overwrite them in DTL3 when it runs.
No Deadline for this Job. Anyone can Take the Task. Transaction ID : 9LX91299SE176464C
Please Note that i can't upload the DTL3 Excel File here cause its 4MB, So i Uploaded to Dropbox and Pasted it Below.
I can have a look, given that it's not urgent
Sure go ahead take your time. I an raising it up to 20 USD Max cause I think this Task is pretty Tough.
The ZQ File is just a dummy file. Meant to just key in the value we Obtained from other sources. You can just use a New workBook with the only four important sheet's instead of ZQ File
Most likely going to change the Objective Since It has been a month. Gonna assume that this Task is not worth 15 USD
Will Put up a New thread in Three Days. Probably going to be About adding one Function in VBA on Adjustable Rows
Hi, apologies this got skipped for so long. Was busy with family matters (new member), and lost track. If you are still looking for this, I will work on it. As compensation for the delay, without any payment. Let me know.
Oh and Merry Christmas.
Oh no please Continue, I still do want this task to be completed. And the 15 USD Offer still stands. Free Labour is Just Distasteful. I just sorta want It Done before 20th Jan 2017.
Oh and My Biggest Congratulations & Merry Christmas =)
Thank you very much. Fair enough. hopefully the dropbox link is still active.
Had to Re-Up the Dropbox Link Again Below.
Code sent to OP. Free of charge due to delay from my side, though OP had extended the timelines till 22nd Jan. Just posting it here for posterity.
VB:Sub sUpdateDL3TrainScheduleFromZQFile() Dim strCurrentFeedingSheetName As String Dim strDestinationWorkbook As String Dim wbkDestinationWorkbook As Workbook Dim wksDestinationWorkSheet As Worksheet Dim lngLoopSourceSheetItems As Long Dim lngDestinationRow As Long Dim lngDestinationFirstColumn As Long 'This routine will skip execution for each row which has a "Y" in column H 'So ensure you clear cells in Column H for those rows which needs to be updated With Application.FileDialog(msoFileDialogFilePicker) .AllowMultiSelect = False .Filters.Add "Excel 2003 & above files", "*.xls; *.xlsx", 1 If .Show = -1 Then strDestinationWorkbook = .SelectedItems(1) Else MsgBox "This program will now exit.", vbOKOnly, "File not selected by user" Goto EndRoutine End If End With On Error Goto EndRoutine strCurrentFeedingSheetName = ThisWorkbook.ActiveSheet.Name Set wbkDestinationWorkbook = Workbooks.Open(strDestinationWorkbook) Set wksDestinationWorkSheet = wbkDestinationWorkbook.Worksheets(strCurrentFeedingSheetName) With ThisWorkbook.ActiveSheet For lngLoopSourceSheetItems = 2 To .Range("A" & .Rows.Count).End(xlUp).Row 'Anything other than a "Y" in column H means that it is not updated in the DTL3 file, and needs to be processed If UCase(.Cells(lngLoopSourceSheetItems, 8).Value) <> "Y" Then lngDestinationRow = fIFERROR(.Cells(lngLoopSourceSheetItems, 1).Value, wksDestinationWorkSheet.UsedRange.Columns(1), 0) lngDestinationFirstColumn = fIFERROR(.Cells(lngLoopSourceSheetItems, 2).Value, wksDestinationWorkSheet.UsedRange.Rows(2), 0) If lngDestinationRow * lngDestinationFirstColumn Then wksDestinationWorkSheet.Cells(lngDestinationRow, lngDestinationFirstColumn).Value = .Range("F" & lngLoopSourceSheetItems).Value wksDestinationWorkSheet.Cells(lngDestinationRow, lngDestinationFirstColumn + 1).Value = .Range("E" & lngLoopSourceSheetItems).Value wksDestinationWorkSheet.Cells(lngDestinationRow, lngDestinationFirstColumn + 2).Value = .Range("G" & lngLoopSourceSheetItems).Value .Cells(lngLoopSourceSheetItems, 8).Value = "Y" lngDestinationRow = Empty lngDestinationFirstColumn = Empty Else .Cells(lngLoopSourceSheetItems, 8).Value = "Unable to find the combination. Please check" End If End If Next lngLoopSourceSheetItems End With ExitSub: strCurrentFeedingSheetName = vbNullString strDestinationWorkbook = vbNullString Set wbkDestinationWorkbook = Nothing Set wksDestinationWorkSheet = Nothing lngLoopSourceSheetItems = Empty lngDestinationRow = Empty lngDestinationFirstColumn = Empty Exit Sub EndRoutine: If lngLoopSourceSheetItems Then MsgBox "The program terminated at row number " & lngLoopSourceSheetItems & vbLf & vbLf & "Please check before continuing the match and update process....", vbOKOnly, Err.Description Else MsgBox "Please check what could be the issue...", vbExclamation + vbOKOnly, "Unknown error...." wbkDestinationWorkbook.Close 1 '0 in case you want to close without saving any changes End If Err.Clear: On Error Goto 0: On Error Goto -1 Goto ExitSub End Sub Private Function fIFERROR(ParamArray varInputs()) As Variant On Error Resume Next fIFERROR = Application.Match(varInputs(0), varInputs(1), 0) If fIFERROR = "Error 2042" Then fIFERROR = varInputs(2) Err.Clear: On Error Goto 0: On Error Goto -1 End If End Function
There are currently 1 users browsing this thread. (0 members and 1 guests)