Formula Calculating issue for Vlookup & Index match formula

[Gunasekaran]

Hi Sir,

I will update Vlookup or Index, match from another workbook to my master data (my master data has more than 3L lines), and record Formulas and put them in VBA code. To update the format, it will take more than 30 minutes. How to proceed. There is no reason why VBA does not support this easy formula calculation.

All the way through, I tried. However, I do not know how making it as fast as possible. Excel 64-bit version

Sub Update_MDM_FDSS()

    With Application
    '    CalcMode = .Calculation
       .Calculation = xlCalculationManual
        .ScreenUpdating = False
         .EnableEvents = False
        .DisplayAlerts = False
    End With



cddr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
Range(cddr) = Evaluate("IF(" & cddr & "="""","""",TRIM(" & cddr & "))")

addr = "B1:B" & Cells(Rows.Count, "B").End(xlUp).Row
Range(addr) = Evaluate("IF(" & addr & "="""","""",TRIM(" & addr & "))")

Range("M2").FormulaR1C1 = "=RC[-12]&RC[-11]"
Range("N1").Value = "Global Acc"
Range("O1").Value = "Global Desc"
Range("P1").Value = "Type"
Range("Q1").Value = "MEP_Code"
Range("Q2").FormulaR1C1 = "=RC[-16]&""_""&RC[-10]"

    Range("N2").FormulaR1C1 = "=VLOOKUP(RC[-1],'C:\ME Financial Report\ME_Financial_Project_File\[MDM_Con_File.xlsb]Sheet1'!C1:C5,5,0)"
    Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-2],'C:\ME Financial Report\ME_Financial_Project_File\[MDM_Con_File.xlsb]Sheet1'!C1:C6,6,0)"
   
'Range("N2").FormulaR1C1 = "=VLOOKUP(RC[-1],'C:\ME Financial Report\ME_Financial_Project_File\[MDM_Con_File.xlsb]Sheet1'!C1:C5,5,0)"
Range("P2").FormulaR1C1 = "=IF(LEFT(RC[-2],1)=""1"",""BS"",(IF(LEFT(RC[-2],1)=""2"",""BS"",""PL"")))"
Range("Q2").FormulaR1C1 = "=RC[-16]&""_""&RC[-10]"
'Range("R2").FormulaR1C1 = "=INDEX(FDSS_Busorg_Map.xlsx!C6,MATCH(RC[-1],FDSS_Busorg_Map.xlsx!C3))"

Range("R2").FormulaR1C1 = "=INDEX('C:\ME Financial Report\ME_Financial_Project_File\[FDSS_Busorg_Map.xlsx]FDSS_Busorg_Map'!C6,MATCH(RC[-1],'C:\ME Financial Report\ME_Financial_Project_File\[FDSS_Busorg_Map.xlsx]FDSS_Busorg_Map'!C3))"
 
   
'lastrow = ActiveSheet.Range("A65536").End(xlUp).Row
Range("M2:O2").AutoFill Destination:=Range("M2:O" & Range("A" & Rows.Count).End(xlUp).Row)
Range("P2:R2").AutoFill Destination:=Range("P2:R" & Range("A" & Rows.Count).End(xlUp).Row)
   
   
Application.CutCopyMode = False
lr = ActiveSheet.Range("A65536").End(xlUp).Row

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        '.Calculation = CalcMode
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
      End With

ActiveWorkbook.Save

Set rng2 = Range(Cells(1, 13), Cells(lr, 18))

rng2.Value2 = rng2.Value2
End Sub

 

[BobBridges]

Some clarifying questions:

1) This may not matter, but you say your master worksheet has "3L lines". In English we usually say 3K for 3 thousand and 3M for 3 million. In Roman numerals, I think L indicates multiples of 50 but I doubt you mean your master sheet has 150 rows. What do you mean by "3L"?

2) You also say you want to "record formulas". I suspect you just mean you want to make a VBA program write the formulae into your worksheet; is that right?

3) You say it will require more than half an hour to "update the format", but I don't think you mean "format"; if I read the first part correctly, you want the program to change the formula, right? I'm guessing that you want to write a VLOOKUP or INDEX or MATCH formula into many rows of your master worksheet. Are you doing it manually or using VBA? If you're trying to do it using VBA, and it's taking 30 minutes to run, yeah, it sounds like you have a lot of rows to do—especially if, as I see from your sample code, you're first turning off a lot of the features that slow Excel down.

I won't try to read your program in detail until I have a clearer understanding of what you're attempting and what's going wrong. Then we'll talk about ways to make it work better, or faster, or just correctly if it's not working right. Is it working right, by the way? Is it producing an error, or just taking a long time?

 

[Gunasekaran]

Some clarifying questions:

1) This may not matter, but you say your master worksheet has "3L lines". In English we usually say 3K for 3 thousand and 3M for 3 million. In Roman numerals, I think L indicates multiples of 50 but I doubt you mean your master sheet has 150 rows. What do you mean by "3L"? - It means 3 Laksh Lines

2) You also say you want to "record formulas". I suspect you just mean you want to make a VBA program write the formulae into your worksheet; is that right? - Need which is best way to Update Vlookup or Index formula by VBA code. (Recording Macro not working for me) but Above mentionted code isnt recorded Macro....

3) You say it will require more than half an hour to "update the format", but I don't think you mean "format"; if I read the first part correctly, you want the program to change the formula, right? I'm guessing that you want to write a VLOOKUP or INDEX or MATCH formula into many rows of your master worksheet. Are you doing it manually or using VBA? If you're trying to do it using VBA, and it's taking 30 minutes to run, yeah, it sounds like you have a lot of rows to do—especially if, as I see from your sample code, you're first turning off a lot of the features that slow Excel down. - Please give me the right VBA code to me. also easy way to update this formula.

I won't try to read your program in detail until I have a clearer understanding of what you're attempting and what's going wrong. Then we'll talk about ways to make it work better, or faster, or just correctly if it's not working right. Is it working right, by the way? Is it producing an error, or just taking a long time? is it ok above answers

 

[BobBridges]

No, your answers were not adequate. But it may not be your fault; maybe your English is too limited to enable you to do better . I may be able to help anyway.

I've looked at your program, and I cannot be sure it's all correct. But if it is all correct—if it gives correct results, and your only problem is that it takes a long time to run—I can see three places in the program that may be the cause. Two of them I may be able to improve.

1) Those two Evaluate statements seem to be submitting all of columns A and B to the Trim function. I don't know how Evaluate(Trim) will work when Calculate is set to Manual, but it's possible that this is part of the problem. One way I've found to speed up Excel programs, when they're updating many cells in a worksheet, is to load the worksheet values into an array, change the data there, and then put the array back in the worksheet. This is more work to program, but when I'm updating thousands of cells it can change a 60-second execution to the blink of an eye. That part of your program might look like this:

' I prefer to use row and column numbers rather than A1 notation; you can translate to your own usage if you want.
Set ocs = ActiveSheet.Cells
rZ = ocs(Rows.Count, 1).End(xlUp).Row 'find the last row in col 1 (that is, col A)
ar = Range(ocs(1, 1), ocs(rZ, 2)).Value 'A1:B<lastrow>
For jr = 2 to rZ 'in each row, Trim both cells
  ar(jr, 1) = Trim(ar(jr, 1))
  ar(jr, 2) = Trim(ar(jr, 2))
  Next jr
Range(ocs(1, 1), ocs(rZ, 2)).Value=ar

I haven't tested this code to be sure I didn't make an error, but my point is that Excel can apparently load thousands or tens of thousands of cell values into an array in a flash, and put them back just as quickly. So updating the values may be a lot faster this way.

2) Another part of your program is doing something similar: You're using AutoFill to copy various formulae from row 2 down through the rest of the worksheet. Again, it may be that doing this in an array will be faster. This time, though, instead of reading and writing the Values of those cells we'll use FormulaR1C1, like this:

' We still have rZ calculated from the earlier part of the program.
' We don't need to read the values in cols M through R; in this case you're replacing what's there.
ReDim ar(1 to rZ, 12 to 17) 'columns M through R
For jr = 2 to rZ
  ar(jr, 12) = "=RC[-12]&RC[-11]"
  ar(jr, 13) = "=VLOOKUP(RC[-1],'C:\ME Financial Report\ME_Financial_Project_File\[MDM_Con_File.xlsb]Sheet1'!C1:C5,5,0)"
  ar(jr, 14) = "=VLOOKUP(RC[-2],'C:\ME Financial Report\ME_Financial_Project_File\[MDM_Con_File.xlsb]Sheet1'!C1:C6,6,0)"
  ar(jr, 15) = "=IF(LEFT(RC[-2],1)=""1"",""BS"",(IF(LEFT(RC[-2],1)=""2"",""BS"",""PL"")))"
  ar(jr, 16) = "=RC[-16]&""_""&RC[-10]"
  ar(jr, 17) = "=INDEX('C:\ME Financial Report\ME_Financial_Project_File\[FDSS_Busorg_Map.xlsx]FDSS_Busorg_Map'!C6,MATCH(RC[-1],'C:\ME Financial Report\ME_Financial_Project_File\[FDSS_Busorg_Map.xlsx]FDSS_Busorg_Map'!C3))"
  Next jr

' Now put the formula into the worksheet cells.
Range(ocs(1, 12), ocs(rZ, 17)).FormulaR1C1 = ar 'M1:R<lastrow>

One warning: I'm not sure whether the array in this case should be dimmed as "1 to rZ, 12 to 17" or as "1 to rZ, 1 to 6"; you may have to experiment to find out which works. But again, the point is that it may go much faster if you put the values into an array and then load them to the worksheet.

3) The third thing that may be causing your program to run slowly is simply the fact that many of these formulae refer to values in an external worksheet. It may be that there's no way around that—when you turn calculation back on to Automatic, maybe Excel is always going to take a while to look up all those values. If so, there may not be much you can do about it.

But you can at least find out. Try running your program without turning those various settings back on at the end, Calculation and ScreenUpdating etc; see how long the program takes to run. Then turn them back on, manually, one by one. If your program runs fast, and it's when you turn on ScreenUpdating or Calculation that the delay occurs, then you know the problem isn't in your program but simply in Excel's ability to handle all that data. And you can try out those two suggestions I listed above, and see how much difference they make too, again, one at a time.

 

[Gunasekaran]

Thanks a lot for your great Clarification and Code. last Pain area for me.

In Activeworkbook I have below sheets
1.Baan ETB
2.Interim Master Data
3.Interim Acc Validation

I have to Update in "Interim Acc Validation" sheets, Respective MEP_Code Lines in Range("B4:E4") .

(MEP_Code Value available in ("Baan ETB") Sheets Range.P2.

Unable to do this VBA code this task. kindly help me sir.. when i run this code. my MEP_Code Value shows "Blank instead of Value. Kindly help and fix this code sir.

Sub Interim_Acc1()

Dim wss, wss1, wss2, wss3, wss4 As Worksheet
Dim rgdata As Range, rgcriteria As Range, rgoutput As Range
Dim rg As Range


Set wss1 = ThisWorkbook.Sheets("Interim Acc Validation")
Set wss2 = ThisWorkbook.Sheets("Baan ETB")
Set wss4 = ThisWorkbook.Sheets("Info")
Set Trg = ThisWorkbook.Sheets("Interim Master Data")
Set rrr = ThisWorkbook.Sheets("30 FDSS VALIDATION")

wss1.Unprotect password:="1234"

Set rg = ThisWorkbook.Sheets("Interim Acc Validation").Range("A4:H10000")
rg.Clear

wss2.Select
Set rng = wss2.Range("P2:P10000")
With rng
    .Value = Evaluate(Replace("If(@="""","""",Trim(@))", "@", .Address))
End With

mep = wss2.Range("P2").Value

Trg.AutoFilterMode = False
If mep <> "" Then

    Sheets("Interim Master Data").Range("A1:E10000").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("Baan ETB").Range("P1:P2"), _
        CopyToRange:=Sheets("Interim Acc Validation").Range("A4"), Unique:=False

wss1.Activate
wss1.Rows(4).EntireRow.Delete
lr = wss1.Range("A" & Rows.Count).End(xlUp).Row


Sheets("Interim Acc Validation").Range("A1").Value = UCase(mep)

 With Sheets("Interim Acc Validation").Cells.Font
        .Name = "Calibri"
        .Size = 9
    End With
 
End Sub

 

[Marc L]

Hi, when you manually apply the formula to the entire range how long does it take ?​

​

 

[Gunasekaran]

Sir this same code I was applied in my Loop condition. This action will perform more than 286+ Excel files. For your easy reference. I just copy and paste this specific code here, Instead of my Full VBA code. Thanks for your understanding


If you share correct code or easy array condition or any other easy method VBA code. Kindly share it to me. ..Same I will insert in middle of my code. - Sir.

 

[Marc L]

Well you misread …​

As my question is based upon your initial post where you wrote​

« There is no reason why VBA does not support this easy formula calculation. »​

So if it takes time manually it will take at least the same time using the same feature under VBA …​

​

 

[Gunasekaran]

No sir, this is my second question in same connection topic. For first topic question, I received Answer from Mr. BobBridges

 

[BobBridges]

Sorry, I've been falling behind. I've never used the CurrentRegion property before, but when I experimented with it, it didn't quite behave the way I expected:

  Set org = Range(ocs(1, 1), ocs(99, 99))
  Set org = org.CurrentRegion

Here I set up a region of colums 1 through 99 and rows 1 through 99; when I took the CurrentRegion of that range, it returned, simply, A1. I don't know why, because in that sheet I have data in rows 1 through 16 and columns 4 through 8.

When I set org to a cell with data in it, it behaved more explainably, returning the range of cells around that cell that had data in it.

Your program takes a large area (A1:E10000) and then does a CurrentRegion on that. Maybe you need to stop the program at that point and see what range that produces. I suspect it's one empty cell, no matter what the documentation says about it.