Help Needed with Loop for Array Function in VBA

[sax2play]

Hello All,

I am at wits end trying to make a loop work for an array function (and I am very new to VBA...). Here is my current code:

Sub Comparison()
'
' Comparison Macro
'

Dim x As Integer
Application.ScreenUpdating = False
NumRows = Range("B2", Range("B2").End(xlDown)).Rows.Count
Range("O2").Select
For x = 1 To NumRows
Selection.FormulaArray = _
"=INDEX($K:$K,SMALL(IF(COUNTIF($E2,$M:$M)*COUNTIF($C2,$L:$L)*COUNTIF($H2,$N:$N),MATCH(ROW($M:$M),ROW($M:$M)),""""),ROWS($A$1:$A$1)),COLUMNS($A$1:$A$1))"
ActiveCell.Offset(1, 0).Select
Next
Application.ScreenUpdating = True


End Sub


The issue I am having is that the relative cells are not increasing as the loop progresses down the sheet. I need $E2, $C2 and $H2 to change to $E3, $C3 and $H3, etc. as the loops progresses.

I cannot simply copy/paste the function to the bottom of the range as it errors out due to a lack of computer memory, hence the loop. The columns that I need to compare vary in length based on the comparison I am attempting to run and some have thousands of rows.

Thanks in advance!!

 

[Fluff]

Hi & welcome to MrExcel.
No need to loop, try

Range("O2").FormulaArray = _
   "=INDEX($K:$K,SMALL(IF(COUNTIF($E2,$M:$M)*COUNTIF($C2,$L:$L)*COUNTIF($H2,$N:$N),MATCH(ROW($M:$M),ROW($M:$M)),""""),ROWS($A$1:$A$1)),COLUMNS($A$1:$A$1))"
Range("O2").Resize(numrows).FillDown

but you should avoid using whole column references,, especially in an array formula

 

[sax2play]

Wow, thank you so much Fluff!

I tried this:

Sub Comparison()

      Application.ScreenUpdating = False
      numrows = Range("B2", Range("B2").End(xlDown)).Rows.Count
      Range("O2").FormulaArray = _
        "=INDEX($K:$K,SMALL(IF(COUNTIF($E2,$M:$M)*COUNTIF($C2,$L:$L)*COUNTIF($H2,$N:$N),MATCH(ROW($M:$M),ROW($M:$M)),""""),ROWS($A$1:$A$1)),COLUMNS($A$1:$A$1))"
      Range("O2").Resize(numrows).FillDown
      Application.ScreenUpdating = True
      
End Sub

It worked perfectly!

How can I eliminate whole column references when the column length varies based on the sheet I am comparing?

Thanks again!

 

[Fluff]

You can use

Range("O2").FormulaArray = _
 "=INDEX($K:$K,SMALL(IF(COUNTIF($E2,$M2:$M2000)*COUNTIF($C2,$L2:$L2000)*COUNTIF($H2,$N2:$N2000),MATCH(ROW($M2:$M2000),ROW($M2:$M2000)),""""),ROWS($A$1:$A$1)),COLUMNS($A$1:$A$))"

just change the 2000 to something that is larger than you need without going over the top.

 

[sax2play]

I guess I am trying to make it too complicated...

Thank you so much for your help!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[sax2play]

I was wondering if you could assist again, Fluff?

The array from above returns a numerical value into a column and another array does the same into a second column. I then copy and paste the results of the array into the two columns next to the original and convert the columns to numbers. So, I have two columns (Q and R) that have numbers based on the results of the array; however there will never be a number in both columns on the same row and some rows will be completely blank (see below):

	2118569​
	2118570​
2118570​
2118569​
2118575​

I would like to search both columns and if either row has a number, paste that number into another column (column A).

Thanks again in advance if you can assist!

 

[sax2play]

Nevermind - I figured it out. Thanks!