How can I modify code to recognize fields with text data?

[makeitwork09]

I have Excel 2007

As part of a test of the conversation from one of our currentvendor systems (v9) to the vendor system upgrade (v11), where data is stored in Microsoft SQL server, one of programmers created and ran a reconciliation. One of the result sets returns rows from each of the systems where some where in the record, data does not match. The reconciliation, however, does not report the specific field(s) that do not match. Note too that the result set is in an intranet website.

The goal of the macro I would like to create is to identify the fields, for each table identified as having differences by the programmer's reconciliation.

Each table that was identified by the programmers reconcilation program is in a worksheet with the retails for the records that have a difference(s) in one or many of its fields. Being that each table has a different schema and differing amounts of data, the amount of rows and columns vary. Also, the data set looks something like the following where fields can be a date, a number, or text and some field values can be blank.


Version# FIELDNAME1 FIELDNAME2 FIELDNAME3 FIELDNAME#...

What I have done thus far is allocate rows, at the top of dataset, for each table for the v9 subtotal, v11 subtotal, and total subtotal for each field, in each worksheet. The macro creates the formula for each of those, as well as a formula that is difference between the v9 total and the v11 subtotal. Then a formula, in one cell, counts the instances where the difference is not zero to help identify the number of fields with differences.

A B C (etc.)......
diff
v9 subtotal
v11 sutotal
subtotal
Version# FIELDNAME1 FIELDNAME2 FIELDNAME#...

The macro then loops through the diff range looking for the column where the difference is not equal to zero. When it finds that column it uses offset to get the field name, holding all such field names for that worksheet (table name), finds the table name in the summary sheet using the name of worksheet, and places the list of fields with differences in the summary sheet.

The issue I have is that this only works for fields that are numbers or dates, since dates are serial numbers in Excel. I cannot get it to return the field name, if the data in the field is text. I modifyed the above formulas to include a sumproduct formula if the field contained text, however, if a table has serveral rows and several columns the calculation of formulas caused the program to take hours to run. That is not acceptable.

Thus, what I need is an effiencient way to also identify the fields that have differences between the versions if the field data is text.

Here is what I currently have.

Sub CreateFormulas()

    Dim LastRow As Long
    Dim LastCol As Long
    Dim lcrow As Long
    Dim lccol As Long
    Dim wsName As Worksheet
    Dim i As Integer
    Dim DiffRange As Range
    Dim FoundRange As Range
    Dim SheetName As String
    Dim FieldName As String
    Dim NameCount As Integer
    
    prodVersion = ThisWorkbook.Worksheets(1).Range("F11").Value
    upgradeVersion = ThisWorkbook.Worksheets(1).Range("F13").Value

For i = wsCount To Worksheets.Count
        With ThisWorkbook.Worksheets(i)
            .Activate
            SheetName = .Name
            FieldName = ""
            .Range("B3").Value = "diff"
            .Range("B4").Value = "v" & prodVersion
            .Range("B5").Value = "v" & upgradeVersion
            .Range("B6").Value = "subtotal"
            
            Call GetRowsCols(lcrow, lccol)
            LastRow = lcrow
            LastCol = lccol

'v9 subtotal
.Range(Cells(4, 3), Cells(4, LastCol)).FormulaR1C1 = "=SUMIF(R8C1:R" & LastRow & "C1,""=v" & prodVersion & """,R8C:R" & _ LastRow & "C)"

'v11 subtotal
.Range(Cells(5, 3), Cells(5, LastCol)).FormulaR1C1 = "=SUMIF(R8C1:R" & LastRow & "C1,""=v" & upgradeVersion & """,R8C:R" & LastRow & "C)"

'difference between prod and upgrade
.Range(Cells(3, 3), Cells(3, LastCol)).Formula = "=IF(COUNTIF(R8C:R" & LastRow & "C,""*"")<>0,IF(AND(ROUND(R[3]C,0)<>ROUND(R[2]C,0),ROUND(R[3]C,0)<>ROUND(R[1]C,0)),1,0),R[1]C-R[2]C)"

'subtotal
.Range(Cells(6, 3), Cells(6, LastCol)).Formula = "=SUBTOTAL(9,C8:C" & LastRow & ")"

'find the columns with differences and update the summary sheet with the names of the fields
            NameCount = 0
            
            For Each DiffRange In .Range(Cells(3, 3), Cells(3, LastCol))
                If DiffRange.Value <> 0 Then
                    FieldName = FieldName + DiffRange.Offset(4, 0).Value & Chr(10)
                End If
            Next DiffRange
            
            Set FoundRange = ThisWorkbook.Worksheets(SummaryWSName).Cells.Find(What:=SheetName, after:=ActiveCell, LookIn:=xlValues, LookAt _
                    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                    False, SearchFormat:=False)
            
            If Not FoundRange Is Nothing Then
                If Len(FieldName) <= 1 Then
                    FoundRange.Offset(0, 6).Value = FieldName
                Else
                    FoundRange.Offset(0, 6).Value = Mid(FieldName, 1, Len(FieldName) - 1)
                End If
                        
            End If

        End With
            
    Next i
    
    Application.ReferenceStyle = xlA1

    
End Sub

Thanks

 

[makeitwork09]

This has been solved. Someone helped me by the use of multiple dimentional arrays, rather than use formulas like I had.