Conditional rank in VBA, using Variant arrays

Hi,

I have a very large dataset, which has about 20,000 rows (companies) and 300 columns (months) and I am trying to rank the companies based on their value each month. However, I want to do a conditional rank each month based on a few criteria for each company). Given that there are a few different sheets with this amount of data, using worksheet formulas makes the file massive and slows Excel to a crawl, so I am hoping to do this with VBA and simply return the ranks as values to the worksheet cells.

I would like to use this formula in VBA. I have verified that it works, just too slow. It ranks the company if it has a number for that month.

=IF(ISNUMBER(Data!I7),1+COUNTIFS(Data!I$7:I$50,">"&Data!I7),"").

FYI, I've removed other criteria from the countifs function for simplicity.

I have figured out how to use WorksheetFunction.CountIfs, but I want to be able to use two For loops to iteratively do this calculation for each cell in a column, then move to the next column and do the same, etc... so can't say WorksheetFunction.CountIfs(Worksheets("Data").Range("I7:I50") because I want it to later change to J7:J50, K7:k50 etc...

I thought that if I assign the entire dataset to a Variant variable, I could hopefully reference each column in the array as needed. Something like WorksheetFunction.CountIfs(DataRange(1:LastRow, I:I),">"&DataRange(J,I)) where I is the column increment in the For Loop, and J is the row increment. However, this obviously does not work because I am not referencing the arrays properly.

I have attached a subset of the data and removed some of the conditions, but the process should be the same for the full file once I get this part figured out. I have also left the original worksheet functions in place so you can see how it works. I would like to replace these with hard values using VBA.

Any insight that you experts might have would be very much appreciated!

Thanks!