Index and Match array in VBA

[Lefemmenikita]

Hi

I am dealing with thousands of rows of data.

I am trying to use the multiple criteria index and match in a VBA formula to return a result.

The formula I am currently using is:

Selection.FormulaArray ="=IF(RC[-3]=""Billable"",INDEX(Coding!C1:C5,MATCH(1,(Coding!C1=Data!RC11)*(Coding!C2=Data!RC1),0),3),INDEX(Coding!C1:C5,MATCH(1,(Coding!C1=Data!RC11)*(Coding!C2=Data!RC1),0),4))"
    Range("P2").Select
   
    Columns("P:P").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

This runs out of memory before cycling through all the cells in the column and returning a result

My question is:

Is there a way to make an index/match formula use a dynamic column reference? (therefore, eliminating the need to have an "if" statement in the above array formula)?

Also, is there a way to have an array index/match formula execute via VBA without running out of memory?

I am able to do this outside of VBA, though it takes around 5 mins for Excel to finish calculating the result before I am able to replace the formula with the calculated value.


Hope my question makes sense

Thanks

 

[offthelip]

I just had a thought have you applied any filters to the sheets? because if you have then the code won't work properly, you must make sure that both sheets have the filters turned off or "all" selected on both

 

[Lefemmenikita]

Just tested it again after removing all autofilters from both pages.

Still getting the same result where the result only looks up to row 44 and everything else shows as 'not found'

 

[offthelip]

use the debugger and check what value you have got in the variable Lastdata. It would appear that you have gotthe number 43 in this variable.
How many rows of data have you got in column A of your DATA sheet ? 43?
as a temproray measusre change the line:

[COLOR=#333333]lastdata = .Cells(Rows.Count, "A").End(xlUp).Row[/COLOR]

to

Lastdata=200

and see whether you get 200 rows populated.
finally learn how to use the debugger and the locals window to work out what is going wrong yourself, because I am just guessing.

 

[Fluff]

Try changing this line as shown

 Next j
' write the results out
 Range(.Cells(1, 16), .Cells([COLOR=#ff0000]lastdata[/COLOR], 16)) = Results
End With

 

[Lefemmenikita]

Try changing this line as shown

 Next j
' write the results out
 Range(.Cells(1, 16), .Cells([COLOR=#ff0000]lastdata[/COLOR], 16)) = Results
End With

I was just coming here to post that I changed that part of the code and it worked!

Thank you

 

[Lefemmenikita]

use the debugger and check what value you have got in the variable Lastdata. It would appear that you have gotthe number 43 in this variable.
How many rows of data have you got in column A of your DATA sheet ? 43?
as a temproray measusre change the line:

[COLOR=#333333]lastdata = .Cells(Rows.Count, "A").End(xlUp).Row[/COLOR]

to

Lastdata=200

and see whether you get 200 rows populated.
finally learn how to use the debugger and the locals window to work out what is going wrong yourself, because I am just guessing.

Thanks for all your help! It is finally working how I want it to after I changed this part of the code:

' write the results out
 Range(.Cells(1, 16), .Cells(Lastdata, 16)) = Results

 

[NScott85]

I have been able to get this code to work for the most part with my own dataset. However, I am only getting the first 34 rows of data to return results. After that, I get "Not Found" still. In the debugger I see that LastData contains all rows of my data. From what I can tell, I think I am not iterating the rows correctly since my data is a slightly different setup.

My lookup is being done on the tab called "Measure Fields". Using the two columns GI and GN from Measure Fields, I need to match to columns A and B on "ValidMeasureDescriptions". The results I need are located in Col C.

Here is my current code:

Sub Test()

Dim LastMeasureDesc, LastData, j, i As Integer
Dim Data, Results, MeasureDesc, One As Range


With Worksheets("ValidMeasureDescriptions")
 LastMeasureDesc = .Cells(Rows.Count, "A").End(xlUp).Row
 MeasureDesc = Range(Cells(1, 1), Cells(LastMeasureDesc, 3))
End With


With Worksheets("Measure Fields")
 LastData = .Cells(Rows.Count, "A").End(xlUp).Row
 Data = Range(.Cells(1, 1), .Cells(LastData, 198))
 
 
 Range(Cells(2, 198), Cells(LastData, 198)) = "Not found"
   Results = Range(.Cells(1, 198), .Cells(LastData, 198))


' I assume there is a header row so I start on row 2
For j = 2 To LastData
  For i = 2 To LastMeasureDesc
  
Debug.Print "MeasureDesctiption: " & (MeasureDesc(i, 1))
Debug.Print "Data: "; (Data(j, 191))

Debug.Print "MeasureDesctiption: " & (MeasureDesc(i, 2))
Debug.Print "Data: " & (Data(j, 196))

    If MeasureDesc(i, 1) = Data(j, 191) And MeasureDesc(i, 2) = Data(j, 196) Then
     ' code and description match so check billable
       Results(j, 1) = MeasureDesc(i, 3)
      End If
     Exit For
   Next i
 Next j
 
' write the results out
 Range(.Cells(1, 198), .Cells(LastData, 198)) = Results
End With
 
End Sub

 

[offthelip]

you are missing a number of "dots" in the code which might be the cause of your probelms. Also you have got some declarations which probably aren't what you intended
try this, I have annotated the code:

Sub Test()

Dim LastMeasureDesc, LastData, j, i As Integer ' this line only defines I as an integer all the others will be variant

Dim Data, Results, MeasureDesc, One As Range ' ditto
' Note however that the code does work because VBA usually works perfectly Ok if everything is defined as variant

With Worksheets("ValidMeasureDescriptions")
 LastMeasureDesc = .Cells(Rows.Count, "A").End(xlUp).Row
 MeasureDesc = Range(.Cells(1, 1), .Cells(LastMeasureDesc, 3)) ' dots missing
End With


With Worksheets("Measure Fields")
 LastData = .Cells(Rows.Count, "A").End(xlUp).Row
 Data = Range(.Cells(1, 1), .Cells(LastData, 198))
 
 
 Range(.Cells(2, 198), .Cells(LastData, 198)) = "Not found"  ' Dots missing
   Results = Range(.Cells(1, 198), .Cells(LastData, 198))


' I assume there is a header row so I start on row 2
For j = 2 To LastData
  For i = 2 To LastMeasureDesc
  
Debug.Print "MeasureDesctiption: " & (MeasureDesc(i, 1))
Debug.Print "Data: "; (Data(j, 191))

Debug.Print "MeasureDesctiption: " & (MeasureDesc(i, 2))
Debug.Print "Data: " & (Data(j, 196))

    If MeasureDesc(i, 1) = Data(j, 191) And MeasureDesc(i, 2) = Data(j, 196) Then
     ' code and description match so check billable
       Results(j, 1) = MeasureDesc(i, 3)
      End If
     Exit For
   Next i
 Next j
 
' write the results out
 Range(.Cells(1, 198), .Cells(LastData, 198)) = Results
End With
 
End Sub

 

[NScott85]

I have used this new code and its still only matching the first set. As it is iterating the rows, I don't think it's iterating down the "ValidMeasureDescription" tab as it should. Using my debug.print I can see the following pair below doesn't match up. I would want to see Showerhead-Gas DHW-Joint HEA for both lines.

MeasureDesctiption: Advanced Power Strip-$10 Copay-Joint HEA
Data: Showerhead-Gas DHW-Joint HEA

 

[NScott85]

I have used this new code and its still only matching the first set. As it is iterating the rows, I don't think it's iterating down the "ValidMeasureDescription" tab as it should. Using my debug.print I can see the following pair below doesn't match up. I would want to see Showerhead-Gas DHW-Joint HEA for both lines.

MeasureDesctiption: Advanced Power Strip-$10 Copay-Joint HEA
Data: Showerhead-Gas DHW-Joint HEA

Here's a mini-sheet if that helps:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK	DL	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA	EB	EC	ED	EE	EF	EG	EH	EI	EJ	EK	EL	EM	EN	EO	EP	EQ	ER	ES	ET	EU	EV	EW	EX	EY	EZ	FA	FB	FC	FD	FE	FF	FG	FH	FI	FJ	FK	FL	FM	FN	FO	FP	FQ	FR	FS	FT	FU	FV	FW	FX	FY	FZ	GA	GB		GD	GE	GF	GG	GH	GI	GJ	GK	GL	GM	GN	GO	GP
1	Vendor Project ID	Measure Description	Quantity	Measure ID IC	External Rebate Processor ID	Nicor Gas Tracking Number	Incentive Nicor	Builder Incentive	Rater Incentive	Gross Annual Therm Savings	Manufacturer	Model	Serial	Measure Life	Post Installation Efficiency	Pre Installation Efficiency	Baseline Efficiency	Derating base	Derating eff	Equipment Efficiency Percent	N_Heat	Capacity Existing Equipment	Capacity New Equipment	Baseline Production Capacity	Pre Install Value	Post Install Value	Equivalent Full Load Hours	Operation Hours Day	Operation Days Year	Annual Therm Consumption	As Built Therms	Base Therms	Size	Household Factor	Degree of Setback	Restaurant Type	Number of Pans	Savings Factor	Calculation Method	Total Sq Feet Above Grade	Total Sq Feet Below Grade	Thermal Regain Factor	Showers Per Person Per Day	HDD	Framing Factor Wall	Framing Factor Attic	Adjustment Factor	R Value Existing Above ground	R Value Existing Below ground	Length of Faucet Use	In Service Rate	Energy per Gallon	Deemed Therm Savings	Length	Length of Other Components	Circumference of Pipe	Furnace Heating Load	Lbs	Duty	Griddle Width	Griddle Depth	ENERGY STAR Equipment Number of Preheats per Day	Baseline Number of Preheats per Day	ENERGY STAR Preheat Time	Baseline Preheat Time	ENERGY STAR Preheat Rate	Baseline Preheat Rate	ENERGY STAR Idle Energy Rate	Baseline Idle Energy Rate	ENERGY STAR Idle Time	Baseline Idle Time	ASTM Energy to Food	Energy Savings per Preheat	Δ Daily Idle Energy	Δ Daily Preheat Energy	Δ Daily Cooking Energy	Bare Pipe Heat Loss	Insulated Pipe Heat Loss	Percent Fossil Fuel	Faucets per Household	Drain Factor	Utilization Factor	Usage	ηDHW	ΔT	Hot Water Reduction Factor	Tout	Tin	Heating Reduction	Steam loss per Trap	Heat of Vaporization	Leaking and Blow-thru	Gallons per Day	Standby Loss	U Coefficient	γWater	Age of Equipment	Level of Repairs Completed	Existing Unit Status	Equipment Location	Insulation Material	Incentive Paid Date	Installation Date	Purchase Date	Approval Date	Audit Date	Start Date	End Date	Total Measure Cost Labor Materials	Incremental Cost	Rebate Unit of Measure	Savings Unit of Measure	Incentive other utility	Incentive other non-utility	Customer Cost	Customer Copay	Estimated Rebate	Incentive Rate	Incentive Cap	Estimated Installation Cost	Estimated Maintenance Cost	Maintenance Cost	Estimated Simple Payback	Simple Payback	Estimated Gross Annual Therm Savings	Estimated Annual Water Savings	Annual Water Savings	Estimated Annual Sewer Savings	Annual Sewer Savings	Estimated Gross kWh Savings	Gross Annual kWh Savings	As Built kWh	Base kWh	Blower Door Results ACH50	Mechanical Ventilation Rate CFM	Mechanical Ventilation Type	System Type	Steam Trap Type	Steam System Type	Number of Recommended ECMs	Conditioned Basement	Calculated Delta Savings Percent	Measure Notes	Number of Scantrons Returned	Teacher Name	Mini Grant Amount	Retailer for Self-Install	Bundle Component	Sub Contractor Type	Horsepower	Pre Output Capacity	Post Output Capacity	Measure Description Detail	Efficiency of Heat Recovery	Pipe Calculation Method	Pipe Materials	Measure Application	Pipe Size	Insulation Size	Elbows	Tees	Flanges	Valves	Measure Type	Measure Gross kWh Savings	Measure Gross kW Savings	Measure Level Water Savings	Total Water Savings	Total Gross kWh Savings	Total Gross kW Savings	Measure Fuel Type	CDD	Total Electric Heating Penalty	Bulb_Type	Replaced_Bulb_Watt	Installed_Bulb_Watt	New_Freezer_Type	New_Refrigerator_Product_Category	Exhaust_Fan_Usage_Type	Existing Duct Condition	Post Sealing Duct Condition	Weatherization_Materials	Post Sealing Duct Condition2	Weatherization Materials		Equipment Type	Original Thermostat Type	Length of Pipe Insulation Added	Incentive_Nicor_PT__c	Location__c	Vendor_Measure_Name__c	RecordTypeId	Measure_Detail__c	Measure_Category__c	EndOfImportColumns	Validators	Errors	Change Measure Name
2	WO-4152772	Advanced Power Strip-$10 Copay-Joint HEA	2	a0P3u00000j2H88EAE			53.40			0			Unknown																																																																																										1/17/2023		1/17/2023	1/17/2023			$53.40	$53.40	Unit	Unit	$0.00	$0.00		10																																																DIM	Direct Install		206	0.0232				Electric																			Measure Name 1							Not found
3	WO-4152846	Advanced Power Strip-$10 Copay-Joint HEA	1	a0P3u00000j1lvuEAA			26.70			0			Unknown																																																																																										1/10/2023		1/10/2023	1/10/2023			$26.70	$26.70	Unit	Unit	$0.00	$0.00		10																																																DIM	Direct Install		103	0.0116				Electric																			Measure Name 2							Not found
4	WO-4153001	Advanced Power Strip-$10 Copay-Joint HEA	2	a0P3u00000j2FVOEA2			53.40			0			Unknown																																																																																										1/17/2023		1/17/2023	1/17/2023			$53.40	$53.40	Unit	Unit	$0.00	$0.00		10																																																DIM	Direct Install		206	0.0232				Electric																			Measure Name 3					DIM		Not found
5	WO-4153338	Advanced Power Strip-$10 Copay-Joint HEA	3	a0P3u00000j2nKkEAI			80.10			0			Unknown																																																																																										1/24/2023		1/24/2023	1/24/2023			$80.10	$80.10	Unit	Unit	$0.00	$0.00		10																																																VA	Direct Install		309	0.0348				Electric																			Measure Name 3					VA		Not found
Measure Fields