Hi Team,
I have a below function
Public Function RowCountMatches(fileName As String) As String
Dim TDSheet As Worksheet
Dim DB2Sheet As Worksheet
Dim lRow1, lRow2 As Long
Dim lCol As Long
Dim TdsheetCount, db2sheetCount As Integer
'Dim fileName1 As String
'Set fileName1 = fileName
Set TDSheet = ActiveWorkbook.Sheets(2)
Set DB2Sheet = ActiveWorkbook.Sheets(3)
lRow1 = TDSheet.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
lCol1 = TDSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
lRow2 = DB2Sheet.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
lCol2 = DB2Sheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
If lRow1 <> lRow2 Then
' write log for missing rows in text file
Call compareAndWriteMissingRows(fileName)
End If
End Function
In compareAndWriteMissingRows function
Public Sub compareAndWriteMissingRows(fileName As String)
Dim rcell As Range
Dim x, y, z As Long
Dim strLastCol As String
Dim lngLastCol As Long
Dim CellData As String
Dim i, j As Integer
Dim bool As Boolean
Dim Status As String
'Dim RowCountMatches As String
Status = "FALSE"
If x > y Then ' TD Sheet has more rows than DB2 Sheet
With Sheets("Norm Result")
lngLastCol = .Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column
strLastCol = Split(.Cells(1, lngLastCol).Address, "$")(1)
For Each rcell In .Range(.Cells(2, 1), .Cells(x, z))
For i = 1 To rcell.Rows.Count
For j = 1 To rcell.Columns.Count
If rcell.Value <> Sheets("Teradata Result").Cells(rcell.row, rcell.Column) Then
'If j = rcell.Columns.Count Then
'Sheets("Compared Result").Cells(rcell.row, rcell.Column) = "FALSE"
'Sheets("Compared Result").Cells(rcell.row, rcell.Column).Interior.ColorIndex = 3
bool = False
Open "C:\EEMS_SIT\Macro Test\Anand Test\LOGFILE.txt" For Output As #1
Write #1, "Run Date is : " & Date
CellData = ""
CellData = "(" & rcell.row & "," & rcell.Column & ")" & "rows are missing in DB2" & fileName
Write #1, CellData,
Close #1
'Write #1, Sheets("TeraData Result").Cells(rcell.row, rcell.Column)
'Else
'Sheets("Compared Result").Cells(rcell.row, rcell.Column) = "TRUE"
End If
j = j + 1
Next j
Next i
Next rcell
End With
End If
If (bool = False) Then
RowCountMatches = Status
End If
i am getting above error as function call on left-hand side of assignment vba in below code
If (bool = False) Then
RowCountMatches = Status
End If
Please help me on this
Anand R
Bookmarks