VBA Code - Create Log sheet (Errorlog) in case VBA Macro condition is not met

[surevyas1984]

Hello Team,
I have created the Macro and I am running multiple macro, if condition is not met then i am getting popup of error message, however i need to create error log sheet where all the errors can be captured in another sheet in case condition is not met. Below is the code for your reference. Appreciate your help

Sub COMBINE()
Call GSTR1
Call GSTR2
Call GSTR3
Call GSTR4


Application.ScreenUpdating = False

' Display a message when the task is completed
MsgBox "Validation done successfully!", vbInformation

End Sub


Sub GSTR1()

Dim lastRow As Long
Dim i As Long

lastRow = Cells(Rows.Count, 9).End(xlUp).Row ' Assuming data starts from row 1

For i = 9 To lastRow
If Not IsEmpty(Range("A" & i).Value) And IsEmpty(Range("BL" & i).Value) Then
MsgBox "Blank cell in column BL at cell BL" & i & ""
End If
Next i

End Sub

Sub GSTR2()

Dim lastRow As Long
Dim i As Long

lastRow = Cells(Rows.Count, 9).End(xlUp).Row ' Assuming data starts from row 1

For i = 9 To lastRow
If (Range("C" & i).Value = "EXWOP" Or Range("C" & i).Value = "EXWP") And Range("AJ" & i).Value = "G" Then
If IsEmpty(Range("AC" & i).Value) Or IsEmpty(Range("AD" & i).Value) Or IsEmpty(Range("AE" & i).Value) Then
MsgBox "Blank cell(s) in columns AC, AD, or AE at row " & i & ""
End If
End If
Next i
End Sub

Sub GSTR3()

Dim lastRow As Long
Dim i As Long

lastRow = Cells(Rows.Count, 9).End(xlUp).Row ' Assuming data starts from row 1

For i = 9 To lastRow
If Not IsEmpty(Range("A" & i).Value) And IsEmpty(Range("BB" & i).Value) Then
MsgBox "Blank cell in column BB at cell BB" & i & ""
End If
Next i

End Sub

Sub GSTR4()

Dim lastRow As Long
Dim i As Long

lastRow = Cells(Rows.Count, 9).End(xlUp).Row ' Assuming data starts from row 1

For i = 9 To lastRow
If Not IsEmpty(Range("A" & i).Value) And IsEmpty(Range("AY" & i).Value) Then
MsgBox "Blank cell in column AY at cell AY" & i & ""
End If
Next i

End Sub

 

[Tupe77]

Do you mean something like this?
1. This will create a new ErrorLog sheet for the log.
2. Writes all messages previously received as "msgbox" notifications to the ErrorLog sheet.

Option Explicit
Dim ErrorLogRow As Long
Dim wsErrLog As Worksheet

Sub COMBINE()

Dim i As Long, existWS As Boolean
' Add Sheet ErrorLog if not Exists
Dim wsCurrent As Worksheet: Set wsCurrent = ActiveSheet ' Save active sheet name
For i = 1 To Worksheets.Count 
    If Worksheets(i).Name = "ErrorLog" Then
        existWS = True
        Set wsErrLog = Worksheets("ErrorLog")
    End If
Next i
If Not existWS Then
    ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)).Name = "ErrorLog"
    Set wsErrLog = Worksheets("ErrorLog")
End If
wsCurrent.Select ' Return to active sheet

ErrorLogRow = wsErrLog.Range("A" & wsErrLog.Cells(Rows.Count, "A").End(xlUp).Row).Row ' Row to write Error Log

Call GSTR1
Call GSTR2
Call GSTR3
Call GSTR4


Application.ScreenUpdating = False

' Display a message when the task is completed
Application.ScreenUpdating = True: ErrorLogRow = 0
MsgBox "Validation done successfully!", vbInformation

End Sub

Sub GSTR1()

Dim lastRow As Long
Dim i As Long

lastRow = Cells(Rows.Count, 9).End(xlUp).Row ' Assuming data starts from row 1

For i = 9 To lastRow
If Not IsEmpty(Range("A" & i).Value) And IsEmpty(Range("BL" & i).Value) Then
ErrorLogRow = ErrorLogRow + 1: wsErrLog.Range("A" & ErrorLogRow).Value = "Blank cell in column BL at cell BL" & i & ""
End If
Next i

End Sub

Sub GSTR2()

Dim lastRow As Long
Dim i As Long

lastRow = Cells(Rows.Count, 9).End(xlUp).Row ' Assuming data starts from row 1

For i = 9 To lastRow
If (Range("C" & i).Value = "EXWOP" Or Range("C" & i).Value = "EXWP") And Range("AJ" & i).Value = "G" Then
If IsEmpty(Range("AC" & i).Value) Or IsEmpty(Range("AD" & i).Value) Or IsEmpty(Range("AE" & i).Value) Then
ErrorLogRow = ErrorLogRow + 1: wsErrLog.Range("A" & ErrorLogRow).Value = "Blank cell(s) in columns AC, AD, or AE at row " & i & ""
End If
End If
Next i
End Sub

Sub GSTR3()

Dim lastRow As Long
Dim i As Long

lastRow = Cells(Rows.Count, 9).End(xlUp).Row ' Assuming data starts from row 1

For i = 9 To lastRow
If Not IsEmpty(Range("A" & i).Value) And IsEmpty(Range("BB" & i).Value) Then
ErrorLogRow = ErrorLogRow + 1: wsErrLog.Range("A" & ErrorLogRow).Value = "Blank cell in column BB at cell BB" & i & ""
End If
Next i

End Sub

Sub GSTR4()

Dim lastRow As Long
Dim i As Long

lastRow = Cells(Rows.Count, 9).End(xlUp).Row ' Assuming data starts from row 1

For i = 9 To lastRow
If Not IsEmpty(Range("A" & i).Value) And IsEmpty(Range("AY" & i).Value) Then
ErrorLogRow = ErrorLogRow + 1: wsErrLog.Range("A" & ErrorLogRow).Value = "Blank cell in column AY at cell AY" & i & ""
End If
Next i

End Sub

My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs."