Help Needed with Loop for Array Function in VBA

sax2play

New Member
Joined
Mar 13, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
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!!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,969
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
No need to loop, try
VBA Code:
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
 
Solution

sax2play

New Member
Joined
Mar 13, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
Wow, thank you so much Fluff!

I tried this:
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,969
Office Version
  1. 365
Platform
  1. Windows
You can use
VBA Code:
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

New Member
Joined
Mar 13, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Thank you so much for your help!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,969
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

sax2play

New Member
Joined
Mar 13, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top