Edit formula to remove range

[RAJESH1960]

Hello everyone
I have multiple formulas in a worksheet which are with range and I want to edit them and avoid the range. This is one of the formulas. Some of them are array formulas.

=VLOOKUP(D2,CHOOSE({1,2},'2A'!C7:C10000,'2A'!A7:A10000),2,0)

I am trying to edit the formula by deleting the range 10,000 but I keep getting an error like this

=VLOOKUP(D2,CHOOSE({1,2},'2A'!$C7:$C,'2A'!$A7:$A),2,0)

.
If someone can show me how to edit this formula I can try to edit the rest of the formulas accordingly.

 

[johnnyL]

Can you please help me to convert the formulas into code for the 3 formulas in post #3.? Then it will be easier for me to convert the rest of the formulas into code as the rest of the formulas are similar to the 3 formulas.

The following is an example of the three sheet formulas put into vba code:

' =IFERROR(IFERROR(VALUE(SUBSTITUTE(INDEX('2A'!$C$7:$C$10000,AGGREGATE(15,6,ROW($A$1:$A$9999)/(RIGHT('2A'!$C$7:$C$10000,5)="Total"),ROWS($1:1))),"-Total","")),SUBSTITUTE(INDEX('2A'!$C$7:$C$5963,AGGREGATE(15,6,ROW($A$1:$A$5991)/(RIGHT('2A'!$C$7:$C$5963,5)="Total"),ROWS($1:1))),"-Total","")),"")

    Range("A1").Formula = "=IFERROR(IFERROR(VALUE(SUBSTITUTE(INDEX('2A'!$C$7:$C$10000,AGGREGATE(15,6,ROW($A$1:$A$9999)/(RIGHT('2A'!$C$7:$C$10000,5)=""Total""),ROWS($1:1))),""-Total"","""")),SUBSTITUTE(INDEX('2A'!$C$7:$C$5963,AGGREGATE(15,6,ROW($A$1:$A$5991)/(RIGHT('2A'!$C$7:$C$5963,5)=""Total""),ROWS($1:1))),""-Total"","""")),"""")"
'
' =INDEX('2A'!$B$7:$B$5947,MATCH(A2&D2,'2A'!$A$7:$A$5947&'2A'!$C$7:$C$5947,0))
    Range("B1").Formula = "=INDEX('2A'!$B$7:$B$5947,MATCH(A2&D2,'2A'!$A$7:$A$5947&'2A'!$C$7:$C$5947,0))"
'
' =SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$K$7:$K$9947=0)*('2A'!$I$7:$I$9947=H$1)*('2A'!$J$7:$J$9947))
    Range("C1").Formula = "=SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$K$7:$K$9947=0)*('2A'!$I$7:$I$9947=H$1)*('2A'!$J$7:$J$9947))"

You didn't mention if you wanted any of them as array formulas, if you do, make the appropriate change to the vba code. You will also have to change the location of where you want the the formulas to be placed.

 

[RAJESH1960]

JohnnyL. I got the values in columns H to R after editing your code for one column. I can't edit the formula to get the array formula. You will have to edit at least one of the array formulas so that I can edit the rest of array formulas. There is one more formula which is similar in around 15 columns which I didn't share which also needs to be printed with the help of a code.

=IF(OFFSET($N$1,ROW()-1,MATCH((S$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=S$1*2)*('2A'!$L$7:$L$9947)),0)

This is one of the array formula

=INDEX('2A'!$F$7:$F$5947,MATCH(A2&D2,'2A'!$A$7:$A$5947&'2A'!$C$7:$C$5947,0))

 

[RAJESH1960]

The following is an example of the three sheet formulas put into vba code:

' =IFERROR(IFERROR(VALUE(SUBSTITUTE(INDEX('2A'!$C$7:$C$10000,AGGREGATE(15,6,ROW($A$1:$A$9999)/(RIGHT('2A'!$C$7:$C$10000,5)="Total"),ROWS($1:1))),"-Total","")),SUBSTITUTE(INDEX('2A'!$C$7:$C$5963,AGGREGATE(15,6,ROW($A$1:$A$5991)/(RIGHT('2A'!$C$7:$C$5963,5)="Total"),ROWS($1:1))),"-Total","")),"")

    Range("A1").Formula = "=IFERROR(IFERROR(VALUE(SUBSTITUTE(INDEX('2A'!$C$7:$C$10000,AGGREGATE(15,6,ROW($A$1:$A$9999)/(RIGHT('2A'!$C$7:$C$10000,5)=""Total""),ROWS($1:1))),""-Total"","""")),SUBSTITUTE(INDEX('2A'!$C$7:$C$5963,AGGREGATE(15,6,ROW($A$1:$A$5991)/(RIGHT('2A'!$C$7:$C$5963,5)=""Total""),ROWS($1:1))),""-Total"","""")),"""")"
'
' =INDEX('2A'!$B$7:$B$5947,MATCH(A2&D2,'2A'!$A$7:$A$5947&'2A'!$C$7:$C$5947,0))
    Range("B1").Formula = "=INDEX('2A'!$B$7:$B$5947,MATCH(A2&D2,'2A'!$A$7:$A$5947&'2A'!$C$7:$C$5947,0))"
'
' =SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$K$7:$K$9947=0)*('2A'!$I$7:$I$9947=H$1)*('2A'!$J$7:$J$9947))
    Range("C1").Formula = "=SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$K$7:$K$9947=0)*('2A'!$I$7:$I$9947=H$1)*('2A'!$J$7:$J$9947))"

You didn't mention if you wanted any of them as array formulas, if you do, make the appropriate change to the vba code. You will also have to change the location of where you want the the formulas to be placed.

From you, I was expecting the code to print the values (without Formulas) directly in the 2A Extract sheet which I would resize the data and get the data for all rows.

 

[johnnyL]

JohnnyL. I got the values in columns H to R after editing your code for one column. I can't edit the formula to get the array formula. You will have to edit at least one of the array formulas so that I can edit the rest of array formulas. There is one more formula which is similar in around 15 columns which I didn't share which also needs to be printed with the help of a code.

=IF(OFFSET($N$1,ROW()-1,MATCH((S$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=S$1*2)*('2A'!$L$7:$L$9947)),0)

This is one of the array formula

=INDEX('2A'!$F$7:$F$5947,MATCH(A2&D2,'2A'!$A$7:$A$5947&'2A'!$C$7:$C$5947,0))

' =IF(OFFSET($N$1,ROW()-1,MATCH((S$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=S$1*2)*('2A'!$L$7:$L$9947)),0)
    Range("D1").Formula = "=IF(OFFSET($N$1,ROW()-1,MATCH((S$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=S$1*2)*('2A'!$L$7:$L$9947)),0)"

And for the Array Formula:

' =INDEX('2A'!$F$7:$F$5947,MATCH(A2&D2,'2A'!$A$7:$A$5947&'2A'!$C$7:$C$5947,0)) ... Array Formula
    Range("E2").FormulaArray = "=INDEX('2A'!$F$7:$F$5947,MATCH(A2&D2,'2A'!$A$7:$A$5947&'2A'!$C$7:$C$5947,0))"

 

[johnnyL]

From you, I was expecting the code to print the values (without Formulas) directly in the 2A Extract sheet which I would resize the data and get the data for all rows.

I have given you code to change the formulas to just the values in previous projects of yours.

 

[excelNewbie22]

hi johnnyL, sorry to interrupt, couldn't pm you or get your attention to a thread i opened, wonder if you can please consider to check it out?

adjusting a macro "generating combinations by a range"

hi! i got helped from johnnyL with the macro below, and it helped a lot, and lately from Alex Blakenburg for ignoring blanks in the range, (and sorry to say alex if you reading this, but when i tested it more, it doesn't work properly, it gave me combinations of 5 and 2... it didn't ignore the...

www.mrexcel.com

if not appropriate i'm sorry and an admin can delete it

 

[RAJESH1960]

I have given you code to change the formulas to just the values in previous projects of yours.

Ok. I will try.

 

[johnnyL]

Let us know how it goes @RAJESH1960 .

 

[RAJESH1960]

Let us know how it goes @RAJESH1960 .

Sure. Still working on it.

 

[RAJESH1960]

Got 32 columns right but getting an error in 3 columns. Columns U and V are showing errors, if this is corrected then maybe column AH will be auto corrected.
One more issue is that I don't know where to call this code in the already existing code.
2. formula to correct.xlsm