Hello! I just started working with VBA this summer, and I have found so many answers to my questions by just searching this forum! Finally ran into a problem that I wasn't able to find a solution to by searching, and I decided to post and see if y'all could help.
I am using Excel 2013 on Windows 7 to create a code that imports data from other Excel files and daily reports and sorts, sums like categories, etc. At this point, the user has already imported data from the database into the workbook using other modules (in sheet ProdSchd). My main sub allows the user to select multiple daily production files, then loops through and opens each file to extract the data. The main sub then calls the following function to find the date in the previously imported information, then returns that row so another sub can import the additional daily data into that row.
Sometimes, a daily report will be produced for a date that is not in the database. In this case, I am attempting to have my code 1.) Check to see if the date was a typo 2.) If the date is not a typo, enter a blank row under the previous date, then continue to import the daily production data. When stepping through the function, the code will run correctly until the line that inserts the row. The row is not inserted into the worksheet and Excel returns to the main function without executing the remaining code. Does anyone see any glaring errors or have any ideas as to why this happens?
I have had trouble with Excel randomly ending subs in other modules. Is there some overarching mistake that I am making? Is there anything I can do in the future to avoid this? Thanks for taking the time to look at my question!
I am using Excel 2013 on Windows 7 to create a code that imports data from other Excel files and daily reports and sorts, sums like categories, etc. At this point, the user has already imported data from the database into the workbook using other modules (in sheet ProdSchd). My main sub allows the user to select multiple daily production files, then loops through and opens each file to extract the data. The main sub then calls the following function to find the date in the previously imported information, then returns that row so another sub can import the additional daily data into that row.
Sometimes, a daily report will be produced for a date that is not in the database. In this case, I am attempting to have my code 1.) Check to see if the date was a typo 2.) If the date is not a typo, enter a blank row under the previous date, then continue to import the daily production data. When stepping through the function, the code will run correctly until the line that inserts the row. The row is not inserted into the worksheet and Excel returns to the main function without executing the remaining code. Does anyone see any glaring errors or have any ideas as to why this happens?
Code:
Function date_row(dailyReport As Worksheet, Loads As Worksheet, ProdSchd As Worksheet, wkbToCopy As Workbook) As Long
'Declarations
Dim day As Date
Dim FoundCell As Excel.Range
Dim answer As String
day = dailyReport.Range("B1").Value 'Date of Report
answer = day
'Check to see if date is valid
Do While (IsDate(day) = False) Or IsEmpty(dailyReport.Range("B1").Value)
day = InputBox("Please enter the correct date for file: '" & wkbToCopy.name & "' as mm/dd/yy.")
dailyReport.Range("B1").Value = day
Loop
'If date not found, get user input
Set FoundCell = ProdSchd.Range("A:A").Find(What:=day, LookAt:=xlWhole)
Do While FoundCell Is Nothing And Not answer = "None"
answer = InputBox(day & " not found for file " & wkbToCopy.name & ". Please enter correct date. If the plant did not bag on this day, please enter 'None")
Set FoundCell = ProdSchd.Range("A:A").Find(What:=answer, LookAt:=xlWhole)
Loop
'If date is typo, then find the correct date inputted by the user
If Not answer = "None" Then
date_row = FoundCell.row
day = answer
dailyReport.Range("B1").Value = day
'If date is not in ProdSchd, enter new row under previous day
Else
Do While FoundCell Is Nothing
day = day - 1
Set FoundCell = ProdSchd.Range("A:A").Find(What:=day, LookAt:=xlWhole, searchdirection:=xlPrevious)
Loop
date_row = FoundCell.row + 1
'PROBLEM OCCURS IN THE FOLLOWING ROW
ProdSchd.Range(Cells(date_row, 1), Cells(date_row, 20)).EntireRow.Insert
ProdSchd.Cells(date_row, 1).Value = day
'Debug.Print ProdSchd.Cells(date_row, 1).Value
End If
End FunctionI have had trouble with Excel randomly ending subs in other modules. Is there some overarching mistake that I am making? Is there anything I can do in the future to avoid this? Thanks for taking the time to look at my question!
