Compare 2 Worksheets - Show List of Variances on 3rd Sheet

I was thinking of much the same thing. Are all the columns (excluding Column A) that do not need to have changes monitored contain only text, and the columns that do need monitoring contain only numerical values?

No, the columns that do not need changes monitored will have a mix of words and #s. I attached a sample file that might better illustrate. the section in orange would not need to be monitored but would vary in column count.

OK, will the first column that needs monitoring always be formatted as Currency as it is in the image you showed?

Yes it would. From that point on, only currencies will be analyzed.

ok thanks, that is great, I can do the code so that it will automatically get the correct starting column, no need for user input.

Here is the file, the code will find the correct starting column dynamically.

thanks a bunch. i tried to give a test run but there is an subscript out of range in Line 25.

You are getting the error when using the version 1.6 file I attached? It runs perfectly for me.

If the error occurs when you copy the code to your working file then I need to see that working file.

that is strange. I am using your file. downloaded and opened it without changing anything.

if i add an On Error Resume Next on the lane before the code runs but then results in all columns getting analyzed/monitored. with the below error on the headerrowrange

z(i - 1, iii) = x(i, ii)

from the debug:

z(i = 2 and iii=3 (z=subscript out of range)

x(i=2 and ii=0 (x=subscript out of range)

I was looking at the header "HDRS" value and when it gets to the subscript error it reads 1 to 71.

there are 17 headers in v 1.6

17*4 is 68 + 3 is 71

so if the code would start monitoring for changes shouldn't it start at Column H (to Column Q is 10 Columns).

if it would account for 4 each that would be 40 + the info columns (A-G) 7 columns = 47 ?

You have not noticed the parts of the formula that are in parentheses!

ReDim z(1 To UBound(x, 1) - 1, 1 To UBound(x, 2) * 4 - ((lCol - 1) * 3))

Ubound(x,1) = 20, UBound(x,2) = 17 and lCol (which is the Column where monitoring needs to start) = 8

so

z(1 To 8 - 1, 1 To 17 * 4 - ((8 - 1) * 3)) equates to z(1 to 7, 1 to 68 - (7 * 3)) which, in turn, = z(1 To 7, 1 To 68 - 21) = z( 1 To 7, 1 To 47)

Hdrs is the ReDimmed 1 to UBound(z, 2), which is 1 to 47

Rename the file you are testing with and send it to me I can then see if something weird happened when Uploading and/or Downloading.

Here you go. just renamed it to 1.6b.

I tested it on 3 other laptops with different excel versions and unfortunately same error.

i did a F8 step into the code to see how it behaves..

until line 13 variable i will get to 21

here variable iii will result in -4

the next line will add 4 to it and iii is = 0 which then causes the next line to be subscript out of range.

on my side, for some reason variable iCOl is 0 even though the currency is USD.

so as an alternative, maybe we just add a column with a set identifier, which would vary in location, e.g. "XXXX" and after that monitoring would start so that the code could simply try to find 4 x's. (1.6c)

so i changed the currency with finding the cell value XXXX and then start and it works.

Only thing i can't figure out is to tell the code to start after the XXXX dividing column.

I do not know why the code is not picking up the currency formatting for you, possibly because a custom format is being used.

Try this version. It should work.

All you will need to do with you actual file is ensure that all columns up to the first column that need monitoring are formatted as "General"

this one works. not sure why the last one didn't.

thanks for the expert help.

on the last version I had uploaded, v1.7, what the changes need to be for the code to start after the column with XXXX?

All you need do is add 1 to the value of i

    With Original
        For i = 2 To .UsedRange.Columns.Count
            If .Cells(2, i).Value = "xxxx" Then
                lCol = i + 1: Exit For
            End If
        Next
    End With

Note I changed UsedRange.Rows to UsedRange.Columns.

That was an error by me originally, .Rows just happened to work with the sample file but might not with your actual file.

perfect thanks again !!!