Excel macro VBA code comparison of two columns from which one column is not mandatory

Help Something Else
, ,
1

Hi there,

I need help for a macro code which I use within a Robot process.

In my case the excel macro checks data in a file for completeness and correctness.
There are mandatory columns in the file which have to exist in the file and not mandatory columns.
In my example the columns “company code” and “fee” are mandatory columns, if they are missing or false the macro will throw an error.
Next to them, the column “gross fee” is not mandatory and its data should only be checked with the data in column “fee”, if column “gross fee” exists. If it exists, the amount should be the same as in column “fee”. If it doesnt exist, there should be no comparison.

image
image1217×454 11.2 KB

I use following vba code but it doesnt work - do you have any idea what I have to change in it or which vba code will work?

My problem is that I dont know how I can involve the not mandatory columns into the loop of the mandatory columns…

Many thanks for any recommendations!

This is the settings sheet in the excel macro file:

image
image1272×379 16.1 KB

This is the vba code:
Option Explicit

Function Main_Check(ByVal StrFilePath As String) As String
'//Checks all criteria for the correct filling of the template _
Marks all fields that are incorrectly filled in red.

Dim WB As Workbook, WS As Worksheet
Dim i As Long, iNotBot As Long, lEnde As Long, strHeader As String, iNotBot As Long, ii As Long, lColEnde As Long, iCoi As Integer
Dim rngFind As Range, booCheck As Boolean, rngHeader As Range, rngKey As Range, rngUsed As Range, rngHeaderNotBot As Range, rngFindNotBot As Range, rngKeyGrossFee As Range, rngGrossFee As Range
Dim strKey As String, arrKey As String, strKeyGrossFee As String, strGrossFee As String

On Error GoTo ErrorHandler

If StrFilePath = “” Then GoTo ErrorHandler

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'//Template is opened
Set WB = Workbooks.Open(StrFilePath)
Set WS = WB.Worksheets(“Check_file”)

With WS

.Cells.EntireColumn.AutoFit

'//Stores the last row and column to be processed
lEnde = .Cells(.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 2, 2).End(xlUp).Row
lColEnde = .UsedRange.SpecialCells(xlCellTypeLastCell).Column


'//Find the beginning of the 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


.Range(rngFind.Address, .Cells(.UsedRange.SpecialCells(xlCellTypeLastCell).Row, rngFind.Column)).EntireRow.Hidden = False

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

'//booCheck is set to true and on error to false _
Thus, if "True" is passed, the complete file is correct

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

booCheck = IsErrorAll(rngUsed)
        

'//Header Check _
Checks all headers in advance to see if they are present and writes the missing ones in a cell
.Cells(4, 7).Clear
.Cells(4, 8).Clear
For i = Settings.Range("Header_Start").Row + 1 To Settings.Range("Header_Ende").Row - 1
    Set rngHeader = .Range(rngFind, .Cells(rngFind.Row, lColEnde)).Find(what:=Settings.Cells(i, 2).Value, LookIn:=xlValues, lookat:=xlWhole)
    If rngHeader Is Nothing Then
        booCheck = False
        
        .Cells(4, 7).Value = "The following column labels were not found: "
        
        If .Cells(4, 8).Value = "" Then
            .Cells(4, 8).Value = .Cells(4, 8).Value & Settings.Cells(i, 2).Value
        Else
            .Cells(4, 8).Value = "," & .Cells(4, 8).Value & Settings.Cells(i, 2).Value
        End If
        .Cells(4, 8).Interior.Color = vbRed
        
    Else
        
    
    End If
Next i
If booCheck = False Then GoTo Ende

'// Check Not-Bot Columns _
Checks in advance whether Not-Bot columns are available
Set rngFindNotBot = .Cells.Find(what:=Settings.Cells(Settings.Range("NotBot_Start").Row + 1, 2).Value, LookIn:=xlValues, lookat:=xlWhole)
For iNotBot = Settings.Range("NotBot_Start").Row + 1 To Settings.Range("NotBot_Ende").Row - 1
Set rngHeaderNotBot = .Range(rngFindNotBot, .Cells(rngFindNotBot.Row, lColEnde)).Find(what:=Settings.Cells(iNotBot, 2).Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not rngHeaderNotBot Is Nothing Then
    '//Not Bot columns are defined
     strKeyGrossFee = "Gross fee"
     Set rngKeyGrossFee = Settings.Cells(1, 1).EntireColumn.Find(what:=strKeyGrossFee, LookIn:=xlValues, lookat:=xlWhole)
     strGrossFee = Settings.Cells(rngKeyGrossFee.Row, 2).Value
    Else
    strKeyGrossFee = ""
    End If

'//All line items are run through and the individual criteria are checked
For i = rngFind.Row + 1 To lEnde Step 1
    
    '//Company code
    strKey = "Company code"
    Set rngKey = Settings.Cells(1, 1).EntireColumn.Find(what:=strKey, LookIn:=xlValues, lookat:=xlWhole)
    strHeader = Settings.Cells(rngKey.Row, 2).Value
    Set rngHeader = .Range(rngFind, .Cells(rngFind.Row, lColEnde)).Find(what:=strHeader, LookIn:=xlValues, lookat:=xlWhole)
    iCoi = rngHeader.Column
    If .Cells(i, rngHeader.Column).Value Like "####" And InStr(1, .Cells(i, rngHeader.Column).Value, vbLf) = 0 Then
        .Cells(i, rngHeader.Column).Interior.Pattern = xlNone
    Else
        .Cells(i, rngHeader.Column).Interior.Color = vbRed
        booCheck = False
    End If
    
    '//Fee
    strKey = "Fee"
    Set rngKey = Settings.Cells(1, 1).EntireColumn.Find(what:=strKey, LookIn:=xlValues, lookat:=xlWhole)
    strHeader = Settings.Cells(rngKey.Row, 2).Value
    Set rngHeader = .Range(rngFind, .Cells(rngFind.Row, lColEnde)).Find(what:=strHeader, LookIn:=xlValues, lookat:=xlWhole)
    If .Cells(i, rngHeader.Column).Value Like "*,*" Or InStr(1, .Cells(i, rngHeader.Column).Value, vbLf) <> 0 Then
             .Cells(i, rngHeader.Column).Interior.Color = vbRed
              booCheck = False
    Else
        .Cells(i, rngHeader.Column).Interior.Pattern = xlNone
    End If
            
    '//Gross fee
    strKey = "Fee"
    Set rngKey = Settings.Cells(1, 1).EntireColumn.Find(what:=strKey, LookIn:=xlValues, lookat:=xlWhole)
    strHeader = Settings.Cells(rngKey.Row, 2).Value
    Set rngHeader = .Range(rngFind, .Cells(rngFind.Row, lColEnde)).Find(what:=strHeader, LookIn:=xlValues, lookat:=xlWhole)
    
    Set rngFindNotBot = .Cells.Find(what:=Settings.Cells(Settings.Range("NotBot_Start").Row + 1, 2).Value, LookIn:=xlValues, lookat:=xlWhole)
    strKeyGrossFee = "Gross fee"
    Set rngKeyGrossFee = Settings.Cells(1, 1).EntireColumn.Find(what:=strKeyGrossFee, LookIn:=xlValues, lookat:=xlWhole)
    strGrossFee = Settings.Cells(rngKeyGrossFee.Row, 2).Value
    Set rngGrossFee = .Range(rngFindNotBot, .Cells(rngFindNotBot.Row, lColEnde)).Find(what:=strGrossFee, LookIn:=xlValues, lookat:=xlWhole)
    
    If .Cells(i, rngGrossFee.Column).Value Is Nothing Then
       .Cells(i, rngHeader.Column).Interior.Pattern = xlNone
    ElseIf .Cells(i, rngHeader.Column).Value <> .Cells(i, rngGrossFee.Column).Value Then
       .Cells(i, rngHeader.Column).Interior.Color = vbRed
           booCheck = False
    Else
       .Cells(i, rngHeader.Column).Interior.Pattern = xlNone
    End If
            
    
Next i

End With

'//Define results
Ende:

Main_Check = booCheck & “,” & Replace(CStr(rngFind.Address), “$”, “”)

If booCheck = False Then
WS.Cells(7, 7).Value = “Error counter:”
WS.Cells(7, 8).Value = WS.Cells(7, 8).Value + 1
Else
WS.Cells(7, 7).Value = “Check ok”
WS.Cells(7, 8).Value = “”
End If

WB.Close (True)

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

Exit Function

'//If there are other errors, it should exit here and return ERROR
ErrorHandler:
On Error GoTo -1
On Error Resume Next
Main_Check = “ERROR”
WB.Close (True)

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Function