Slicemahn
06-28-2022, 09:13 AM
Hi VBAExpress Nation!
I am hoping to get some help with simplifying some code. It is long and I have spared everyone's eyes from the long reading. Essentially what I would like to do is have the main loop execute from the worksheet but the cell checks from modules. I am not quite sure how I would reference the cells to the modules and have the checks executed. See the code below
If SPFile_Exists(Cells(x, 24).Value) = False Then Application.StatusBar = "Report " & Cells(x, 2).Value & " cannot be archived"
Cells(x, 12).Value = "Error - File Not Found"
Cells(x, 14).Value = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")
Cells(x, 15).Value = Format(Cells(x, 14) - Cells(x, 13), "hh:mm:ss")
ErrorLog.WriteLog Report:=Cells(x, 2).Value, _
ErrorMsg:=Cells(x, 11).Value & " TASK NOT COMPLETED" & "|" & Cells(x, 12).Value, _
Location:=Cells(x, 24).Value, _
Description:="Check the URL file location cannot be found"
ReportErrors = ReportErrors + 1
Else
If ReportCheckOut(Cells(x, 24)) = False Then
Application.StatusBar = "Report " & Cells(x, 2).Value & " cannot be checked out."
Cells(x, 12).Value = "Error - Checked Out Report"
Cells(x, 14).Value = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")
Cells(x, 15).Value = Format(Cells(x, 14) - Cells(x, 13), "hh:mm:ss")
ErrorLog.WriteLog Report:=Cells(x, 2).Value, _
ErrorMsg:=Cells(x, 11).Value & " TASK NOT COMPLETED" & "|" & Cells(x, 12).Value, _
Location:=Cells(x, 24).Value, _
Description:="Report is currently checked out to another user."
ReportErrors = ReportErrors + 1
Else
If Cells(x, 5) <> "Archived" Then
Application.StatusBar = "Report " & Cells(x, 2).Value & " cannot be reactivated"
Cells(x, 12).Value = "Error - Active Report"
Cells(x, 14).Value = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")
Cells(x, 15).Value = Format(Cells(x, 14) - Cells(x, 13), "hh:mm:ss")
ErrorLog.WriteLog Report:=Cells(x, 2).Value, _
ErrorMsg:=Cells(x, 11).Value & " TASK NOT COMPLETED" & "|" & Cells(x, 12).Value, _
Location:=Cells(x, 24).Value, _
Description:="Report is already active"
ReportErrors = ReportErrors + 1
Else
Cells(x, 5).Value = "Active"
Cells(x, 12).Value = "Success"
UpdateMeta Cells(x, 24).Value, Cells(x, 11).Value
Cells(x, 13).Value = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")
Cells(x, 14).Value = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")
Cells(x, 15).Value = Format(Cells(x, 14) - Cells(x, 13), "hh:mm:ss")
Application.StatusBar = "Report " & Cells(x, 2).Value & " is now reactivated"
ReportSuccess = ReportSuccess + 1
End If
End If
End If
So this sample code is in the event that a user selects "Update" for the action taken. There are also "Archive" and "Refresh" actions that are also choices a user can make. In the module do I reference ActiveCell? Do I do it in a With block? I am not sure. This is the reason why I am reaching out to the knowledgeable VBAExpress Nation for help. Thanks.
I am hoping to get some help with simplifying some code. It is long and I have spared everyone's eyes from the long reading. Essentially what I would like to do is have the main loop execute from the worksheet but the cell checks from modules. I am not quite sure how I would reference the cells to the modules and have the checks executed. See the code below
If SPFile_Exists(Cells(x, 24).Value) = False Then Application.StatusBar = "Report " & Cells(x, 2).Value & " cannot be archived"
Cells(x, 12).Value = "Error - File Not Found"
Cells(x, 14).Value = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")
Cells(x, 15).Value = Format(Cells(x, 14) - Cells(x, 13), "hh:mm:ss")
ErrorLog.WriteLog Report:=Cells(x, 2).Value, _
ErrorMsg:=Cells(x, 11).Value & " TASK NOT COMPLETED" & "|" & Cells(x, 12).Value, _
Location:=Cells(x, 24).Value, _
Description:="Check the URL file location cannot be found"
ReportErrors = ReportErrors + 1
Else
If ReportCheckOut(Cells(x, 24)) = False Then
Application.StatusBar = "Report " & Cells(x, 2).Value & " cannot be checked out."
Cells(x, 12).Value = "Error - Checked Out Report"
Cells(x, 14).Value = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")
Cells(x, 15).Value = Format(Cells(x, 14) - Cells(x, 13), "hh:mm:ss")
ErrorLog.WriteLog Report:=Cells(x, 2).Value, _
ErrorMsg:=Cells(x, 11).Value & " TASK NOT COMPLETED" & "|" & Cells(x, 12).Value, _
Location:=Cells(x, 24).Value, _
Description:="Report is currently checked out to another user."
ReportErrors = ReportErrors + 1
Else
If Cells(x, 5) <> "Archived" Then
Application.StatusBar = "Report " & Cells(x, 2).Value & " cannot be reactivated"
Cells(x, 12).Value = "Error - Active Report"
Cells(x, 14).Value = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")
Cells(x, 15).Value = Format(Cells(x, 14) - Cells(x, 13), "hh:mm:ss")
ErrorLog.WriteLog Report:=Cells(x, 2).Value, _
ErrorMsg:=Cells(x, 11).Value & " TASK NOT COMPLETED" & "|" & Cells(x, 12).Value, _
Location:=Cells(x, 24).Value, _
Description:="Report is already active"
ReportErrors = ReportErrors + 1
Else
Cells(x, 5).Value = "Active"
Cells(x, 12).Value = "Success"
UpdateMeta Cells(x, 24).Value, Cells(x, 11).Value
Cells(x, 13).Value = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")
Cells(x, 14).Value = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")
Cells(x, 15).Value = Format(Cells(x, 14) - Cells(x, 13), "hh:mm:ss")
Application.StatusBar = "Report " & Cells(x, 2).Value & " is now reactivated"
ReportSuccess = ReportSuccess + 1
End If
End If
End If
So this sample code is in the event that a user selects "Update" for the action taken. There are also "Archive" and "Refresh" actions that are also choices a user can make. In the module do I reference ActiveCell? Do I do it in a With block? I am not sure. This is the reason why I am reaching out to the knowledgeable VBAExpress Nation for help. Thanks.