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!!
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!!