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.

 

[mrbenhop]

What do you mean you want to avoid the range? If you type $C7:C will not work. You need to reference a valid range for the VLOOKUP to work.

Example C7:C8 or C7:D8 though a Vlookup referencing only one column would be rather pointless in most cases.

 

[RAJESH1960]

How about these.? I have only 2 Vlookup formulas in the whole sheet. But multiple formulas of the same type in many cells. Can these be edited ?

=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))

=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))

 

[RAJESH1960]

The code takes 10 minutes to run and get the result. Maybe due to the range...

 

[johnnyL]

If you are trying to change the 10000 to a smaller range then it would probably end up like C & LastRow/ A & LastRow or something similar. You would have to calculate what the LastRow should be for the particular sheet that you are dealing with.

 

[RAJESH1960]

JohnnyL. Thanks for replying man. I am unable to edit the formula. I tried to edit the formula as you said, but it doesn't accept the formula. I will try to insert the formulas in the sheet with the help of a code. Maybe that may work.

 

[RAJESH1960]

'
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row                                                               '   Get last used row of Column A in wsDestination
'
' =IF(IF($G2<>"",COUNTIFS($B:$B,IF($B2="PORTAL","TALLY","PORTAL"),$C:$C,$C2,$G:$G,">="&($G2-1),$G:$G,"<="&($G2+1)),COUNTIFS($B:$B,IF($B2="PORTAL","TALLY","PORTAL"),$C:$C,$C2,$H:$H,">="&($H2-1),$H:$H,"<="&($H2+1),$I:$I,">="&($I2-1),$I:$I,"<="&($I2+1)))>0,IF(COUNTIFS($B$2:$B2,$B2,$C$2:$C2,$C2,$G$2:$G2,$G2&"",$H$2:$H2,$H2&"",$I$2:$I2,$I2&"")=1,"MATCHED",""),"Not Found")
'
        .Range(DestinationRemarksColumn & "2").Formula = "=IF(IF($G2<>"""",COUNTIFS($B:$B,IF($B2=""PORTAL"",""TALLY""," & _
                """PORTAL""),$C:$C,$C2,$G:$G,"">=""&($G2-1),$G:$G,""<=""&($G2+1)),COUNTIFS($B:$B,IF($B2=""PORTAL""," & _
                """TALLY"",""PORTAL""),$C:$C,$C2,$H:$H,"">=""&($H2-1),$H:$H,""<=""&($H2+1),$I:$I,"">=""&($I2-1)," & _
                "$I:$I,""<=""&($I2+1)))>0,IF(COUNTIFS($B$2:$B2,$B2,$C$2:$C2,$C2,$G$2:$G2,$G2&"""",$H$2:$H2," & _
                "$H2&"""",$I$2:$I2,$I2&"""")=1,""MATCHED"",""""),"""")"                                                 '   Formula to put in DestinationRemarksColumn
'

Something like this from your previous code. The only problem is that the source range starts from the 7th row and the destination row starts from the 2nd row. Also the source sheet has blank rows in the middle at every transaction.

 

[RAJESH1960]

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.

 

[RAJESH1960]

I found one more code where you have removed the range and converted to unlimited rows if it helps.

With Sheets("MasterData")
        .Range("C:E").NumberFormat = "General"                                                  ' Set columns to General format
        '***********************************
        .Range("C2").Formula = "=IFERROR(IF(B2="""","""",IF(VLOOKUP(B2,CopyData!$N$2" & _
                               ":$O$" & Sheets("CopyData").Cells(Rows.Count, 1).End(xlUp).Row & ",2,0)=0,"""",VLOOKUP(B2,CopyData!$N$2" _
                             & ":$O$" & Sheets("CopyData").Cells(Rows.Count, 1).End(xlUp).Row & ",2,0))),"""")"    ' Write updated formula to C2

 

[RAJESH1960]

This is the file in which formulas in 2A sheet need to be converted into code. When I run the code the code will print the values in place of the formulas.
Convert formulas to code.xlsm