VBA experts,

Please help with an explanation as to why this code is failing sporadically.. Lately, it works about 50% of the time.

I'm running Excel 2010 on a Win 7 system

A routine I wrote compares any two worksheets and writes out the differences (on a new sheet -"shtNew") in the form of:

Column A - cell address being compared
Column B - worksheet 1 cell value 'shtSource
Column C - worksheet 2 cell value 'shtTarget

Part of my code traps the range of rows and columns that contain data (uses the max value between sheets being compared), then utilizes this boundary for looping purposes to run the comparison.

Here's how I'm capturing the range boundaries:

Dim lngRow As Long ' max data row
Dim lngCol As Long ' max data col
Dim rngSource As Range ' range of all used cells

Set rngSource = .UsedRange
With rngSource
lngRow = .Rows.Count
lngCol = .Columns.Count
End With

Here's the comparison loop:

With shtSource
For i = 1 To lngRow
For j = 1 To lngCol
If .Cells(i, j) < shtTarget.Cells(i, j) Then
shtNew.Range("A65000").End(xlUp).Offset(1, 0).Activate
ActiveCell = .Cells(i, j).Address 'cell address being compared
ActiveCell.Offset(0, 1) = .Cells(i, j) ' value in cell above on shtSource
ActiveCell.Offset(0, 2) = shtTarget.Cells(i, j) ' ' value in cell above on shtTarget
End If
Next j
Next i
End With


The issue is that the variable lngRow always correctly captures the row number of the last cell entry in the sheet, but lngCol sometimes gives me the max column address of the sheet ("XEX16378"). The net result is that the comparison no longer occurs and the routine returns a blank sheet if the column limit isn't correctly returned.

Interestingly, checking what's being captured when things go haywire gives me a range that is correct with respect to the row, but goes to the limit on columns. For example, a range that should be "A1:Z100" returns as "A1:XEX100"

Any ideas why this is happening?

Art