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
 

roykana

Board Regular
Joined
Mar 8, 2018
Messages
120
Office Version
  1. 2010
Platform
  1. Windows
What means More Custom Records?
And Again I Don't Understand What you want Exactly?
what I mean is I adjusted according to the original data which had about fifty thousand record rows. I tried the vba code from you it runs very slowly and I have also attached the file. which I post there are the first 2 points you have not completed perfectly. I think I have explained enough in my post
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

roykana

Board Regular
Joined
Mar 8, 2018
Messages
120
Office Version
  1. 2010
Platform
  1. Windows
you understand what I say for the first point you have not finished perfectly. maybe you've read my post perfectly right
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,639
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Then you want InputBOx Or Combobox to Select Criteria From Column ITC.
Is it right?
 

roykana

Board Regular
Joined
Mar 8, 2018
Messages
120
Office Version
  1. 2010
Platform
  1. Windows
Then you want InputBOx Or Combobox to Select Criteria From Column ITC.
Is it right?
Yes it right select criteria from column ITM. It is point 2 . how for point 1, then?
 

roykana

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

ADVERTISEMENT

Yes it right select criteria from column ITM. It is point 2 . how for point 1, then?
Use listbox too for point 2
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,639
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
This is For Point 1, I worked on Point 2
VBA Code:
Sub multivlookupV1()
Dim Lr1 As Long, Lr3 As Long, Lr4 As Long, Sh1 As Worksheet, Sh3 As Worksheet, Sh4 As Worksheet
Dim t As Double, i As Long, j As Long, E As Long, k As Long, RunT As Double
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:G" & Lr3).ClearContents
   For i = 2 To Lr3
   If .Range("A" & i).Value = "" Then
      .Range("G" & i).Value = ""
      .Range("B" & i).Value = ""
   Else
    If .Range("E" & i).Value = "M18" Then
       For j = Lr1 To 2 Step -1
       If StrComp(.Range("A" & i).Value, Sh1.Range("D" & j).Value) = 0 Then
        If .Range("G" & i).Value = "" Then
       .Range("G" & i).Value = Sh1.Range("C" & j).Value
       End If
       .Range("B" & i).Value = Sh1.Range("C" & j).Value
       End If
       Next j
     Else
       For k = Lr4 To 2 Step -1
       If StrComp(.Range("A" & i).Value, Sh4.Range("D" & k).Value) = 0 Then
       If .Range("G" & i).Value = "" Then
       .Range("G" & i).Value = Sh4.Range("C" & k).Value
       End If
       .Range("B" & i).Value = Sh4.Range("C" & k).Value
       End If
       Next k
    End If
    End If
Resum2:
  Next i
       '.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
 RunT = Round(Timer - t, 3)

'Notify user in seconds
  MsgBox "This code ran successfully in " & RunT & " seconds", vbInformation
 End Sub
Sub multivlookupV2()
Dim Lr1 As Long, Lr2 As Long, Lr4 As Long, Sh1 As Worksheet, Sh2 As Worksheet, Sh4 As Worksheet
Dim t As Double, i As Long, j As Long, E As Long, k As Long, RunT As Double
t = Timer
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("B2:B" & Lr2).ClearContents
   For i = 2 To Lr2
   If .Range("A" & i).Value = "" Then
      .Range("G" & i).Value = ""
      .Range("B" & i).Value = ""
   Else
      For j = Lr1 To 2 Step -1
       If StrComp(.Range("A" & i).Value, Sh1.Range("D" & j).Value) = 0 Then
        If .Range("G" & i).Value = "" Then
       .Range("G" & i).Value = Sh1.Range("C" & j).Value
       End If
       .Range("B" & i).Value = Sh1.Range("C" & j).Value
       End If
       Next j
      End If
    
Resum2:
  Next i
 End With
 Application.ScreenUpdating = True
 RunT = Round(Timer - t, 3)

'Notify user in seconds
  MsgBox "This code ran successfully in " & RunT & " seconds", vbInformation
 End Sub
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,639
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

AND one other thing ,
Are for you words with uppercase, lowercase or Proper case is different?
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,639
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You Use Exact For Match & lookup function. Exact Used for Differntiate Between UpperCase, LowerCase And Proper Case Words
Example: With Exact : Roykana <> roykana <> ROYKANA Are different.
And with your formula Result is False
1. Is it Correct?
2. And for ListBox, Selection Case where should be Input & also Results?
 

roykana

Board Regular
Joined
Mar 8, 2018
Messages
120
Office Version
  1. 2010
Platform
  1. Windows
You Use Exact For Match & lookup function. Exact Used for Differntiate Between UpperCase, LowerCase And Proper Case Words
Example: With Exact : Roykana <> roykana <> ROYKANA Are different.
And with your formula Result is False
1. Is it Correct?
2. And for ListBox, Selection Case where should be Input & also Results?
Example: With Exact : Roykana <> roykana <> ROYKANA Are different.
1. according to the example you give it is true
2. refers to the listbox then the criteria based on the ITM column in IFG-BOJ & IFG-M18. After I type in criteria, the list will automatically appear in the listbox after I select enter, it will automatically be filled in the itc column in input-m18 & input-boj

with your formula Result is False >>

you mean the formula I made is wrong
 

Watch MrExcel Video

Forum statistics

Threads
1,127,612
Messages
5,625,846
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