MULTI VLOOKUP IN VBA

roykana

Active Member
Joined
Mar 8, 2018
Messages
311
Office Version
  1. 2010
Platform
  1. Windows
Dear All master,


please help me continue with the vba code that I have created according to the results

I made with the formula, namely in columns E, F, G.
this is my link : MULTI VLOOKUP IN VBA.xlsm
file
and I have repair Removed Records: Sorting from /xl/worksheets/sheet2.xml part. Is there a problem with my vba sort code?. Please solution,
VBA Code:
Option Explicit


Sub multivlookup()
    Application.ScreenUpdating = False

    ' sort DB sheet on col A
   With Sheets("DB").Sort
     .SortFields.Add Key:=Range("c1"), Order:=xlAscending
     .SetRange Range("c1:f61")
     .Header = xlYes
     .Apply
    End With
    With Range(Cells(2, 5), Cells(2, 5).End(xlDown))
        .FormulaR1C1 = "=IF(VLOOKUP([@DATE],dbperiod,1,true)=[@DATE],VLOOKUP([@DATE],dbperiod,4),"""")"
        .Value = .Value
    End With
    Application.ScreenUpdating = True
End Sub
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Try it like
VBA Code:
   With Sheets("DB")
      .Range("C1:F61").Sort Key1:=.Range("C1"), order1:=xlAscending, Header:=xlYes
   End With
   With Range(Cells(2, 5), Cells(2, 5).End(xlDown))
       .FormulaR1C1 = "=IF(VLOOKUP([@DATE],dbperiod,1,true)=[@DATE],VLOOKUP([@DATE],dbperiod,4),"""")"
       .Value = .Value
   End With
 
Upvote 0
Solution
Try it like
VBA Code:
   With Sheets("DB")
      .Range("C1:F61").Sort Key1:=.Range("C1"), order1:=xlAscending, Header:=xlYes
   End With
   With Range(Cells(2, 5), Cells(2, 5).End(xlDown))
       .FormulaR1C1 = "=IF(VLOOKUP([@DATE],dbperiod,1,true)=[@DATE],VLOOKUP([@DATE],dbperiod,4),"""")"
       .Value = .Value
   End With
Dear sir
I have tried according to the VBA code that you sent me, that there are still many different results from the formula I created. I upload the revised document,
I beg you to make a VBA code only in column H (PERIOD vba), Column I (CATEGORY vba), Column J (NAME vba) or the one I mark in yellow.
If the results do not match compared to columns E, F, G that I made with the formula, the results will appear not the same
and # N / A in columns k, L, M with the formula I created. so the result must be the same.
Capture.JPG

this is my link : MULTI VLOOKUP IN VBA - R1.xlsm
file
VBA Code:
Option Explicit


Sub multivlookup()
    Application.ScreenUpdating = False

    ' sort DB sheet on col A
  With Sheets("DB")
      .Range("C1:F61").Sort Key1:=.Range("C1"), order1:=xlAscending, Header:=xlYes
   End With
   With Range(Cells(2, 8), Cells(2, 8).End(xlDown))
       .FormulaR1C1 = "=IF(VLOOKUP([@DATE],dbperiod,1,true)=[@DATE],VLOOKUP([@DATE],dbperiod,4),"""")"
       .Value = .Value
   End With
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
This thread has nothing to do with formulae, it was the problem relating to sorts. If you are having problems with your formulae, then you need to start a new thread. Thanks.
 
Upvote 0
This thread has nothing to do with formulae, it was the problem relating to sorts. If you are having problems with your formulae, then you need to start a new thread. Thanks.
Dear sir
i want the vba code like the result of the formula i made. In the file there are 3 formulas. the result must be the same as the formula I created.I want to create in vba or macro
Thanks
Roykana
 
Upvote 0
Dear sir
i want the vba code like the result of the formula i made. In the file there are 3 formulas. the result must be the same as the formula I created.I want to create in vba or macro
Thanks
Roykana
I want to make the whole in vba. I think you understand what I am saying
 
Upvote 0
That is a totally different question & therefore needs a new thread.
Did the code I suggested get rid of the problems you were having with the sort?
 
Upvote 0
That is a totally different question & therefore needs a new thread.
Did the code I suggested get rid of the problems you were having with the sort?
dear sir

for the vba sort problem has been solved.
well I'll make a new one
thanks
roykana
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

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