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

surevyas1984

New Member
Joined
Nov 17, 2023
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.

VBA Code:
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." :devilish:
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top