Problem in vba vlookup

[roykana]

Dear All Master,

The problem I mean is as follows :

1. I want to modify the vba code because it takes too long/very slow to vlookup in VBA code so I want a very fast vba code
2. I want to set from column I2 and J2 in the sheet "GSD" and I mark it in yellow
3. I want vba code which is automatic like "Worksheet Change" or if any other code
this is my link : SALES ALL IN ONE 2016-NOW-vba - Copy.xlsm
file

Option Explicit
Sub multivlookupV2()
 OptimizeVBA True
 Dim startTime As Single, endTime As Single
    startTime = Timer
 With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With
    ActiveSheet.DisplayPageBreaks = False
 With Range(Cells(2, 9), Cells(2, 9).End(xlDown))
       .FormulaR1C1 = "=IF([@ITM]=""JASA SERVICE"",""NO"",IF([@DEPT]=""BOJ"",VLOOKUP([@ITM],MASTER_ITEM_NO,4,0),IF([@DEPT]=""M18"",VLOOKUP([@ITM],MASTER_ITEM_NO[[M18]:[ITEM NO NEW]],3,0),IF([@DEPT]=""MD2"",VLOOKUP([@ITM],MASTER_ITEM_NO[[MD2]:[ITEM NO NEW]],2,0),IF([@DEPT]=""M07"",VLOOKUP([@ITM],MASTER_ITEM_NO[[M18]:[ITEM NO NEW]],3,0))))))"
       .Value = .Value
 End With
  With Range(Cells(2, 10), Cells(2, 10).End(xlDown))
       .FormulaR1C1 = "=VLOOKUP([@PNM],GSG,9,0)"
       .Value = .Value
 End With
 With Application
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
    endTime = Timer
    Debug.Print (endTime - startTime) & " seconds have passed [VBA]"
    OptimizeVBA False
 End Sub
 Sub OptimizeVBA(isOn As Boolean)
    Application.Calculation = IIf(isOn, xlCalculationManual, xlCalculationAutomatic)
    Application.EnableEvents = Not (isOn)
    Application.ScreenUpdating = Not (isOn)
    ActiveSheet.DisplayPageBreaks = Not (isOn)
End Sub

Thanks
Roykana

 

[roykana]

Dear Mr. Akuini,

Thank you for the code you provided. It went perfectly.

To automatically code, so the data comes from the application database that I query via Excel. So I want to get data updates so I use refresh all.

Thanks

Roykana

So what can after the completion of the all refresh process will automatically run the VBA code

 

[Akuini]

So what can after the completion of the all refresh process will automatically run the VBA code

You can call Sub toCall at the end of your code that make the query to the database.

 

[roykana]

You can call Sub toCall at the end of your code that make the query to the database.

Dear Mr. Akuini,


I use power query for the database. So it's not vba code. So if I click refresh all, after the query process is complete, Sub toCall will automatically go straight to it.


Thanks
Roykana

 

[Akuini]

Sorry, I'm not familiar with power query.
Hope others could help.

 

[roykana]

Sorry, I'm not familiar with power query.
Hope others could help.

Dear Mr. Akuini
Ok means there are 2 actions, namely:
1. Refresh all
2. Sub to call

Okay, no problem. This too is quite perfect. Maybe if I have a question with your VBA code then I will immediately chat with you. You don't mind if I ask you directly.

Thanks
Roykana

 

[Akuini]

Maybe if I have a question with your VBA code then I will immediately chat with you. You don't mind if I ask you directly.

Actually, all discussions about Excel problems must go through forum thread.
You can start a new thread & then you can sent PM to me to inform me about your new thread.

 

[roykana]

Actually, all discussions about Excel problems must go through forum thread.
You can start a new thread & then you can sent PM to me to inform me about your new thread.

Dear Mr. Akuini,
Ok Thank you very much.

Thanks

Roykana

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.