VBA UPDATE FORMULA EXACT WITH INPUT TYPE AND POP UP SEARCH BASED CRITERIA

roykana

Board Regular
Joined
Mar 8, 2018
Messages
120
Office Version
  1. 2010
Platform
  1. Windows
Dear All Master,

1. I want the exact formula in the vba code in column B in the sheet "INPUT BOJ" & "INPUT M18" because using an array formula makes it slow because the data to be input is about ten thousand rows.
You can see an example of an array formula that I created in column B. So I want the vba code for the concept or process to be the same as using a formula such as, for example, type enter,
type tab then automatically, copy in column itc then automatically appears in column itm.
2. I want a pop up search based on ITM and help criteria. In the pop up, I just need to enter the selected one and then automatically enter the INPUT-m18 & INPUT-boj sheet in the ITC column.
The row info or original data record in ifg-m18 & ifg-boj is about fifty thousand rows.
I also coded the vba but this is not perfect or maybe there is another solution.

File link : VBA UPDATE FORMULA EXACT WITH INPUT TYPE AND POP UP SEARCH BASED CRITERIA.xlsm
file

Thanks
Roykana
VBA Code:
Option Explicit
Sub multivlookupV1()
Application.ScreenUpdating = False
 With Range(Cells(2, 7), Cells(2, 7).End(xlDown))
       .FormulaR1C1 = "=IF([@ITC]="""","""",IF([@KET]=""M18"",LOOKUP(2,1/EXACT([@ITC],Table_Query_from_TT1_NOW[ITC]),Table_Query_from_TT1_NOW[ITM]),LOOKUP(2,1/EXACT([@ITC],Table_Query_from_now[ITC]),Table_Query_from_now[ITM])))"
       .Value = .Value
 End With
 Application.ScreenUpdating = True
 End Sub
Sub multivlookupV2()
Application.ScreenUpdating = False
  With Range(Cells(2, 7), Cells(2, 7).End(xlDown))
       .FormulaR1C1 = "=IF([@ITC]="""","""",LOOKUP(2,1/EXACT([@ITC],Table_Query_from_TT1_NOW[ITC]),Table_Query_from_TT1_NOW[ITM]))"
       .Value = .Value
 End With
 Application.ScreenUpdating = True
 End Sub
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,639
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Use
VBA Code:
.FormulaArray
Not
VBA Code:
.Formula
 

roykana

Board Regular
Joined
Mar 8, 2018
Messages
120
Office Version
  1. 2010
Platform
  1. Windows
Use
VBA Code:
.FormulaArray
Not
VBA Code:
.Formula
Dear Mr .maabadi,

please provide a solution for points 1 & 2 by modifying the vba code and reply with the excel file that I attached.

Thanks
Roykana
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,639
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this at your WorkSheet:
VBA Code:
Sub multivlookupV1()
Dim Lr1 As Long, Lr3 As Long, Lr4 As Long, Sh1 As Worksheet, Sh3 As Worksheet, Sh4 As Worksheet
Set Sh1 = Sheets("IFG M18")
Set Sh3 = Sheets("INPUT BOJ")
Set Sh4 = Sheets("IFG BOJ")
Lr1 = Sh1.Range("A" & Rows.Count).End(xlUp).Row
Lr3 = Sh3.Range("A" & Rows.Count).End(xlUp).Row
Lr4 = Sh4.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
 With Sh3
       .Range("G2").FormulaArray = "=IFNA(IF(A2="""","""",IF(E2=""M18"",LOOKUP(2,1/EXACT(A2,'IFG M18'!$D$2:$D$" & Lr1 & "),'IFG M18'!$C$2:$C$" & Lr1 & "),LOOKUP(2,1/EXACT(A2,'IFG BOJ'!$D$2:$D$" & Lr4 & "),'IFG BOJ'!$C$2:$C$" & Lr4 & "))),"""")"
       .Range("G2").AutoFill Destination:=.Range("G2:G" & Lr3)
       .Range("G2:G" & Lr3).Value = .Range("G2:G" & Lr3).Value
 End With
 Application.ScreenUpdating = True
 End Sub
Sub multivlookupV2()
Dim Lr1 As Long, Lr2 As Long, Lr4 As Long, Sh1 As Worksheet, Sh2 As Worksheet, Sh4 As Worksheet
Set Sh1 = Sheets("IFG M18")
Set Sh2 = Sheets("INPUT M18")
Set Sh4 = Sheets("IFG BOJ")
Lr1 = Sh1.Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sh2.Range("A" & Rows.Count).End(xlUp).Row
Lr4 = Sh4.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
 With Sh2
       .Range("G2").FormulaArray = "=IFNA(IF(A2="""","""",LOOKUP(2,1/EXACT(A2,'IFG M18'!$D$2:$D$" & Lr1 & "),'IFG M18'!$C$2:$C$" & Lr1 & ")),"""")"
       .Range("G2").AutoFill Destination:=.Range("G2:G" & Lr2)
       .Range("G2:G" & Lr2).Value = .Range("G2:G" & Lr2).Value
 End With
 Application.ScreenUpdating = True
 End Sub
 

roykana

Board Regular
Joined
Mar 8, 2018
Messages
120
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Try this at your WorkSheet:
VBA Code:
Sub multivlookupV1()
Dim Lr1 As Long, Lr3 As Long, Lr4 As Long, Sh1 As Worksheet, Sh3 As Worksheet, Sh4 As Worksheet
Set Sh1 = Sheets("IFG M18")
Set Sh3 = Sheets("INPUT BOJ")
Set Sh4 = Sheets("IFG BOJ")
Lr1 = Sh1.Range("A" & Rows.Count).End(xlUp).Row
Lr3 = Sh3.Range("A" & Rows.Count).End(xlUp).Row
Lr4 = Sh4.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
With Sh3
       .Range("G2").FormulaArray = "=IFNA(IF(A2="""","""",IF(E2=""M18"",LOOKUP(2,1/EXACT(A2,'IFG M18'!$D$2:$D$" & Lr1 & "),'IFG M18'!$C$2:$C$" & Lr1 & "),LOOKUP(2,1/EXACT(A2,'IFG BOJ'!$D$2:$D$" & Lr4 & "),'IFG BOJ'!$C$2:$C$" & Lr4 & "))),"""")"
       .Range("G2").AutoFill Destination:=.Range("G2:G" & Lr3)
       .Range("G2:G" & Lr3).Value = .Range("G2:G" & Lr3).Value
End With
Application.ScreenUpdating = True
End Sub
Sub multivlookupV2()
Dim Lr1 As Long, Lr2 As Long, Lr4 As Long, Sh1 As Worksheet, Sh2 As Worksheet, Sh4 As Worksheet
Set Sh1 = Sheets("IFG M18")
Set Sh2 = Sheets("INPUT M18")
Set Sh4 = Sheets("IFG BOJ")
Lr1 = Sh1.Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sh2.Range("A" & Rows.Count).End(xlUp).Row
Lr4 = Sh4.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
With Sh2
       .Range("G2").FormulaArray = "=IFNA(IF(A2="""","""",LOOKUP(2,1/EXACT(A2,'IFG M18'!$D$2:$D$" & Lr1 & "),'IFG M18'!$C$2:$C$" & Lr1 & ")),"""")"
       .Range("G2").AutoFill Destination:=.Range("G2:G" & Lr2)
       .Range("G2:G" & Lr2).Value = .Range("G2:G" & Lr2).Value
End With
Application.ScreenUpdating = True
End Sub
Dear Maabadi,


I have tried the VBA code you provide. It has been running but provided that the g column in the input-m18 & ifg-md2 sheet must be empty if it is filled and continue the blank, then it cannot use the vba code. Then for the first point I want the vba to code the concept like an excel formula by typing then enter, type then tab, copy in the itc column.

Thanks
roykana
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,639
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I don't understand what you say exactly?
1. You want formula at column G.
Or .... Values .
2. YOU want copy formula from one column to another
 

roykana

Board Regular
Joined
Mar 8, 2018
Messages
120
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

I don't understand what you say exactly?
1. You want formula at column G.
Or .... Values .
2. YOU want copy formula from one column to another
answer to question number 1: I want the value in column G with the same process as the formula but the value may appear if I am not mistaken with the event code vba. answer to question number 2: only in column g it doesn't copy to another but the problem is I use the vba code from you first filled in column g then there is a data record or row that I just tried your vba code can't so I have to clear from g2 to the last then I can use your vba code.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,639
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
If you want first Clear all Values From Column G then Apply Formula Add This line Before Line of FormulaArray at Both Macro Code
VBA Code:
.Range("G2:G" & Lr2).ClearContents
Thus:
VBA Code:
Sub multivlookupV1()
Dim Lr1 As Long, Lr3 As Long, Lr4 As Long, Sh1 As Worksheet, Sh3 As Worksheet, Sh4 As Worksheet
Set Sh1 = Sheets("IFG M18")
Set Sh3 = Sheets("INPUT BOJ")
Set Sh4 = Sheets("IFG BOJ")
Lr1 = Sh1.Range("A" & Rows.Count).End(xlUp).Row
Lr3 = Sh3.Range("A" & Rows.Count).End(xlUp).Row
Lr4 = Sh4.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
With Sh3
       .Range("G2:G" & Lr3).ClearContents
       .Range("G2").FormulaArray = "=IFNA(IF(A2="""","""",IF(E2=""M18"",LOOKUP(2,1/EXACT(A2,'IFG M18'!$D$2:$D$" & Lr1 & "),'IFG M18'!$C$2:$C$" & Lr1 & "),LOOKUP(2,1/EXACT(A2,'IFG BOJ'!$D$2:$D$" & Lr4 & "),'IFG BOJ'!$C$2:$C$" & Lr4 & "))),"""")"
       .Range("G2").AutoFill Destination:=.Range("G2:G" & Lr3)
       .Range("G2:G" & Lr3).Value = .Range("G2:G" & Lr3).Value
End With
Application.ScreenUpdating = True
End Sub
Sub multivlookupV2()
Dim Lr1 As Long, Lr2 As Long, Lr4 As Long, Sh1 As Worksheet, Sh2 As Worksheet, Sh4 As Worksheet
Set Sh1 = Sheets("IFG M18")
Set Sh2 = Sheets("INPUT M18")
Set Sh4 = Sheets("IFG BOJ")
Lr1 = Sh1.Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sh2.Range("A" & Rows.Count).End(xlUp).Row
Lr4 = Sh4.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
With Sh2
       .Range("G2:G" & Lr2).ClearContents
       .Range("G2").FormulaArray = "=IFNA(IF(A2="""","""",LOOKUP(2,1/EXACT(A2,'IFG M18'!$D$2:$D$" & Lr1 & "),'IFG M18'!$C$2:$C$" & Lr1 & ")),"""")"
       .Range("G2").AutoFill Destination:=.Range("G2:G" & Lr2)
       .Range("G2:G" & Lr2).Value = .Range("G2:G" & Lr2).Value
End With
Application.ScreenUpdating = True
End Sub
 

roykana

Board Regular
Joined
Mar 8, 2018
Messages
120
Office Version
  1. 2010
Platform
  1. Windows
If you want first Clear all Values From Column G then Apply Formula Add This line Before Line of FormulaArray at Both Macro Code
VBA Code:
.Range("G2:G" & Lr2).ClearContents
Thus:
VBA Code:
Sub multivlookupV1()
Dim Lr1 As Long, Lr3 As Long, Lr4 As Long, Sh1 As Worksheet, Sh3 As Worksheet, Sh4 As Worksheet
Set Sh1 = Sheets("IFG M18")
Set Sh3 = Sheets("INPUT BOJ")
Set Sh4 = Sheets("IFG BOJ")
Lr1 = Sh1.Range("A" & Rows.Count).End(xlUp).Row
Lr3 = Sh3.Range("A" & Rows.Count).End(xlUp).Row
Lr4 = Sh4.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
With Sh3
       .Range("G2:G" & Lr3).ClearContents
       .Range("G2").FormulaArray = "=IFNA(IF(A2="""","""",IF(E2=""M18"",LOOKUP(2,1/EXACT(A2,'IFG M18'!$D$2:$D$" & Lr1 & "),'IFG M18'!$C$2:$C$" & Lr1 & "),LOOKUP(2,1/EXACT(A2,'IFG BOJ'!$D$2:$D$" & Lr4 & "),'IFG BOJ'!$C$2:$C$" & Lr4 & "))),"""")"
       .Range("G2").AutoFill Destination:=.Range("G2:G" & Lr3)
       .Range("G2:G" & Lr3).Value = .Range("G2:G" & Lr3).Value
End With
Application.ScreenUpdating = True
End Sub
Sub multivlookupV2()
Dim Lr1 As Long, Lr2 As Long, Lr4 As Long, Sh1 As Worksheet, Sh2 As Worksheet, Sh4 As Worksheet
Set Sh1 = Sheets("IFG M18")
Set Sh2 = Sheets("INPUT M18")
Set Sh4 = Sheets("IFG BOJ")
Lr1 = Sh1.Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sh2.Range("A" & Rows.Count).End(xlUp).Row
Lr4 = Sh4.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
With Sh2
       .Range("G2:G" & Lr2).ClearContents
       .Range("G2").FormulaArray = "=IFNA(IF(A2="""","""",LOOKUP(2,1/EXACT(A2,'IFG M18'!$D$2:$D$" & Lr1 & "),'IFG M18'!$C$2:$C$" & Lr1 & ")),"""")"
       .Range("G2").AutoFill Destination:=.Range("G2:G" & Lr2)
       .Range("G2:G" & Lr2).Value = .Range("G2:G" & Lr2).Value
End With
Application.ScreenUpdating = True
End Sub
Dear Mr. Maabadi,
Thank you for your reply sorry if I just replied.
I have tried the VBA code from you and it works and I fixed the ifna formula to iferror because I am using the 2010 version of Excel and I forgot to update it on my profile.
I tried to record more custom records and the VBA code is very long. I attach the results of the time and also the file so you can try. One more thing that I convey is that the process is the same as the formula, namely Worksheet Events calls the vba module.
file link : VBA UPDATE FORMULA EXACT WITH INPUT TYPE AND POP UP SEARCH BASED CRITERIA-answer(maabadi).xlsm
file
Thanks
result timer.PNG

Roykana
VBA Code:
Option Explicit
Sub multivlookupV1()
Dim t
Dim Lr1 As Long, Lr3 As Long, Lr4 As Long, Sh1 As Worksheet, Sh3 As Worksheet, Sh4 As Worksheet
t = Timer
Set Sh1 = Sheets("IFG M18")
Set Sh3 = Sheets("INPUT BOJ")
Set Sh4 = Sheets("IFG BOJ")
Lr1 = Sh1.Range("A" & Rows.Count).End(xlUp).Row
Lr3 = Sh3.Range("A" & Rows.Count).End(xlUp).Row
Lr4 = Sh4.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
With Sh3
       .Range("G2").FormulaArray = "=IFERROR(IF(A2="""","""",IF(E2=""M18"",LOOKUP(2,1/EXACT(A2,'IFG M18'!$D$2:$D$" & Lr1 & "),'IFG M18'!$C$2:$C$" & Lr1 & "),LOOKUP(2,1/EXACT(A2,'IFG BOJ'!$D$2:$D$" & Lr4 & "),'IFG BOJ'!$C$2:$C$" & Lr4 & "))),"""")"
       .Range("G2").AutoFill Destination:=.Range("G2:G" & Lr3)
       .Range("G2:G" & Lr3).Value = .Range("G2:G" & Lr3).Value
End With
Application.ScreenUpdating = True
Debug.Print "It's done in: " & Timer - t & " seconds"
End Sub
Sub multivlookupV2()
Dim Lr1 As Long, Lr2 As Long, Lr4 As Long, Sh1 As Worksheet, Sh2 As Worksheet, Sh4 As Worksheet
Set Sh1 = Sheets("IFG M18")
Set Sh2 = Sheets("INPUT M18")
Set Sh4 = Sheets("IFG BOJ")
Lr1 = Sh1.Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sh2.Range("A" & Rows.Count).End(xlUp).Row
Lr4 = Sh4.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
With Sh2
       .Range("G2").FormulaArray = "=IFERROR(IF(A2="""","""",LOOKUP(2,1/EXACT(A2,'IFG M18'!$D$2:$D$" & Lr1 & "),'IFG M18'!$C$2:$C$" & Lr1 & ")),"""")"
       .Range("G2").AutoFill Destination:=.Range("G2:G" & Lr2)
       .Range("G2:G" & Lr2).Value = .Range("G2:G" & Lr2).Value
End With
Application.ScreenUpdating = True
End Sub
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,639
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
What means More Custom Records?
And Again I Don't Understand What you want Exactly?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,611
Messages
5,625,829
Members
416,138
Latest member
Pizzaman22

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