Excel VBA macro: How to check cells in standardized table per group

[stefi]

Hi there,

In my bot process I use an Excel VBA macro, with this I want to check the cells in a standardized table (meaning fix column headers, but no fix placement so that the table can start anywhere in a fix named sheet).

Here is an example of the table:

Explanation of example table:
The data is for invoicing. In colum S named “invoice ID” there is the ID for the invoice, meaning that row 72 (ID=1) is one invoice with one invoice position, therefore only one amount in total (here 5 EUR). The second invoice (ID=2) contains two invoice positions with one total amount of 10 HKD (including 8 + 2 HKD positions).

With the VBA macro I want to check each cell in the table for the correct entry and format. In addition, for the case of multi-position invoice, I want to check the total amount whether it is the correct sum of the individual positions (in the example for invoice 2: I want to check if the total amount 10 is the correct sum of 8 and 2).
The total amount is always placed in the first row of the invoice group.

I already created this code but it doesn’t work.

Option Explicit
Private WB As Workbook, ws As Worksheet
Private i As Long, lEnde As Long, strHeader As String
Private rngFind As Range, booCheck As Boolean, rngHeader As Range, rngFormula As Range, rngKey As Range, rngUsed As Range
Private idCol As Range
Private headerRow As Range
Private dataRange As Range
Private currentID As Variant
Private previousID As Variant
Private groupStartRow As Long
Private groupEndRow As Long
Private lastRow As Long
Private lastCol As Long
Private numRows As Long 'ProcessGroup
Private isMultiLine As Boolean
Private cell As Range
Private col As Range
Private groupRange As Range
Private groupRow As Range
Private rowIndex As Long
Private cellRef As Range
Private cellValue As Variant
Private cellFormula As String
Private cellFormat As String
Private containsLineBreak As Boolean

Function Main_Check(ByVal strFilePath As String) As String
On Error GoTo ErrorHandler
If strFilePath = “” Then GoTo ErrorHandler
Set WB = Workbooks.Open(strFilePath)
Set ws = WB.Worksheets(“SpecificSheet”)
With ws
'//Define last row and column which must be processed
lEnde = .Cells(.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 2, 2).End(xlUp).Row
lColEnde = .UsedRange.SpecialCells(xlCellTypeLastCell).Column

'//Find beginning of table
Set rngFind = .Cells.Find(what:=Settings.Cells(Settings.Range("Header_Start").Row + 1, 2).Value, LookIn:=xlValues, lookat:=xlWhole)
If rngFind Is Nothing Then
    booCheck = False
    End
End If

lEnde = .Cells(.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 2, 2).End(xlUp).Row

Set rngUsed = .Range(rngFind.Address, .Cells(lEnde, lColEnde))
booCheck = IsErrorAll(rngUsed)

'//Define header row und ID-column
Set headerRow = rngUsed.EntireRow
lastRow = .Cells(.Rows.Count, rngUsed.Column).End(xlUp).Row
lastCol = .Cells(headerRow.Row, .Columns.Count).End(xlToLeft).Column
Set idCol = .Range(.Cells(headerRow.Row + 1, rngUsed.Column), .Cells(lastRow, lastCol))

'//Group for ID
currentID = idCol.Cells(1, 1).Value

    'Check if first group has ID = 1
    If currentID <> 1 Then
        currentID.Interior.Color = vbRed
        booCheck = False
    End If
    If booCheck = False Then GoTo Ende

groupStartRow = idCol.Cells(1, 1).Row
previousID = currentID 'Initialize first group

For i = 2 To idCol.Rows.Count + 1 'Loop via IDs
    If i > idCol.Rows.Count Or idCol.Cells(i, 1).Value <> currentID Then
        'Group end reached
        groupEndRow = idCol.Cells(i - 1, 1).Row
    
        'Process group
        Call ProcessGroup(.Rows(groupStartRow & ":" & groupEndRow), .Rows(headerRow))
    
        'Check ongoing IDs
        If i <= idCol.Rows.Count Then
            Dim nextID As Variant
            nextID = idCol.Cells(i, 1).Value
            If nextID <> previousID + 1 Then
                idCol.Cells(i, 1).Interior.Color = vbRed
                booCheck = False
            End If
            If booCheck = False Then GoTo Ende
            previousID = nextID 'Set the new last ID
        End If
        
        'Start new group
        If i <= idCol.Rows.Count Then
            currentID = idCol.Cells(i, 1).Value
            groupStartRow = idCol.Cells(i, 1).Row
        End If
    End If
Next i

End Function

Sub ProcessGroup(groupRange As Range, headerRow As Range)

'Check if group is multi-line
isMultiLine = (groupRange.Rows.Count > 1)

'Check all columns and all rows of group
rowIndex = 1 'Initialize row index within group
If groupRange.Rows.Count = 1 And groupRange.Columns.Count = 1 Then
Set cellRef = groupRange
Else
Set cellRef = groupRange.Cells(1, 1)
End If

For Each groupRow In groupRange.Rows
Debug.Print “Zeilenwert von groupRow:” & groupRow
Call Processing1(groupRow)

rowIndex = rowIndex + 1 'Increase row index

Next groupRow

End Sub

Sub Processing1(groupRow As Range)
'//Invoice-ID (only formal check, content already checked)
strKey = “ID”
Set rngKey = Settings.Cells(1, 1).EntireColumn.Find(what:=strKey, LookIn:=xlValues, lookat:=xlWhole)
strHeader = Settings.Cells(rngKey.Row, 2).Value
Set rngHeader = ws.Range(rngFind, ws.Cells(rngFind.Row, lColEnde)).Find(what:=strHeader, LookIn:=xlValues, lookat:=xlWhole)
cellRef = ws.Cells(groupRow.Row, rngHeader.Column)
containsLineBreak = (InStr(1, cellRef.Value, vbLf) > 0)

    If (cellRef.Value Like "#" Or cellRef.Value Like "##" Or cellRef.Value Like "###") _
    And cellRef.NumberFormat = "General" And Not containsLineBreak And Not Left(cellRef.Formula, 2) = "=+" Then
        cellRef.Interior.Pattern = xlNone
    Else
        cellRef.Interior.Color = vbRed
        booCheck = False
    End If

… (further checks for further columns)
End Sub

Does anybody have an idea what the problem is or how the code would work?

Many thanks in advance for your help!!!

Stefi