Hi, nbeharry!
First of all and sorry to say, the table at your worksheet wksht is slightly unconventional. Try doing this:
a) Place an asterisk in X6 cell (next column of end table at row vendors).
b) Define a dynamic named ranged DataTable as:
=DESREF(wksht!$A$11;;;CONTARA(wksht!$A:$A)-1;COINCIDIR("*";wksht!$6:$6;0)-1) -----> in english: =OFFSET(wksht!$A$11,,,COUNTA(wksht!$A:$A)-1,MATCH("*",wksht!$6:$6,0)-1)
c) Replace your code:
-----
If Val(sh1.Cells(i, 22)) > 0 Then
-----
by this:
-----
If Val(sh1.Cells(i, Range("DataTable").Columns.Count - 1)) > 0 Then
-----
qualifying Range with Worksheets("wksht"). if VBA code in other worksheet or in a module.
Regards!
PS: Check this link for the fixed file:
https://dl.dropboxusercontent.com/u/60558749/If%20statement%20where%20rows%20are%20a%20variable%20and%20number%20of%20columns%20over%20may%20change.%20-%20NalMGmt%20%28for%20nbeharry%20at%20chandoo.org%29.xlsm