VBA to create an error log based on IF AND statement


Board Regular
Feb 23, 2015
Hello. I'm hoping you all can help me create a macro that will loop through multiple sheets to identify errors and log those on an ErrorLog worksheet. The excel file is a system generated report with 115 tabs. Column A = Descriptions and Column B = Values. If Column A is blank " " (there are four spaces and that is it) and Column B has a value, then that row exported in error and needs to be addressed.

I would like for the macro to identify rows where Column A is blank " " and Column B isNumeric and log that Tab Name and Row Number on the Errorlog worksheet.

Any suggestions on how to modify the code below to create the IF And statement that is needed on how to create an output log for the values that are errors?

Thanks in advance for your help.

Sub FindErrors()

    Dim cell    As Range
    Dim LastRow As Long
    Dim rng     As Range
    Dim wks     As Worksheet
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "ErrorLog"
    For Each Sh In Worksheets
        Set wks = ActiveSheet
        Set rng = wks.Range("A10")
        LastRow = wks.Cells(Rows.Count, rng.Column).End(xlUp).Row
        Set rng = rng.Resize(LastRow - rng.Row + 1, 1)
        With Application
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
            For Each cell In rng
              ' to test for blanks in column A, and if B is a value
                If Left(cell, 4) = "    " And IsNumeric(##I'm not sure what to put here?) Then
                    (###Log True values SheetName and Row Number to ErrorLog?)
                End If
            Next cell
    Next Sh
            .Calculation = xlCalculationAutomatic
            .ScreenUpdating = True
        End With
    End Sub
In the example below row 2 is considered an error.

RowColumn AColumn B
1 Allocation1
3 Benefit2

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...