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

R

roykana

Active Member
Joined
Mar 8, 2018
Messages
311
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Use
VBA Code: 
.FormulaArray
Not
VBA Code: 
.Formula
 
Upvote 0
maabadi said:
Use
VBA Code: 
.FormulaArray
Not
VBA Code: 
.Formula
Click to expand...
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
 
Upvote 0
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
 
Upvote 0
maabadi said:
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
Click to expand...
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
 
Upvote 0
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
 
Upvote 0
maabadi said:
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
Click to expand...
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.
 
Upvote 0
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
 
Upvote 0
maabadi said:
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
Click to expand...
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
 
Upvote 0
What means More Custom Records?
And Again I Don't Understand What you want Exactly?
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
VBA that pulls data based on multiple criteria
Replies
0
Views
322
Mouse7681
M
E
  • Question Question
VBA code clean up
Replies
9
Views
593
mumps
mumps
A
  • Question Question
Xlookup, Vlookup and/or VBA for finding, matching and inputting data to separate sheet
Replies
0
Views
850
AlfredDB
A
E
  • Question Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
545
offthelip
O
W
  • Question Question
VBA Help - Lookup Match Between 2 Worksheets
Replies
0
Views
694
Web_Jim
W

Forum statistics

Threads
1,224,828
Messages
6,181,201
Members
453,022
Latest member
RobertV1609

Share this page

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
Back
Top