Replaced the formula with VBA Code

  • I have been using below formula with enabling the Arrays and convert it into VBA code like this below code.


    When the code paste that formula into the given cells it has locked the Cell A10 which should be changed accordingly with cell reference.


    Any help will be appreciated.





    Code
    1. =IFERROR(INDEX(Table1!$A$1:$DP$27,MATCH($G$9&$E$4,Table1!$E:$E&Table1!$F:$F,0),MATCH(A10,Table1!$6:$6,0)), "")



    Code
    1. Sub coderun52()
    2. Dim LastRowColumnA As Long
    3. LastRowColumnA = Sheet1.Cells(Rows.Count, 4).End(xlUp).Row
    4. Sheet1.Range("G10:G" & LastRowColumnA).FormulaArray = _
    5. "=IFERROR(INDEX(Table1!R1C1:R27C120,MATCH(R9C7&R4C5,Table1!C5&Table1!C6,0),MATCH(RC[-6],Table1!R6,0)), """")"
    6. End Sub
  • Hello,


    You can test the following macro

    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thank you very much one more query i have used this and it took me complete 5 minutes.


    Is there any fastest way to do this.



  • Special Note to: Bhatti and Excel&VBA


    Seems to me ... both of you have planned to be working ... till the Year 2144 ... !!!=O


    Let me wish you both ... very sincerely ... the Best of Luck  :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thank you very much one more query i have used this and it took me complete 5 minutes.


    Is there any fastest way to do this.


    Thanks for your Thanks


    Have you tested to use your own Array Formula over 4'800 cells ...???


    Please do no try ...;)


    Using a VBA Evaluate is by far a lot more efficient ... :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Ahh I wasn't working with him but he is doing similar work. I just posted it here because KJBox was not available since morning i have been working with him since 2 months. He is very great Man and Excel Guru he is back now Thank you Carim.

  • Yes i have tested my own it is working more slower and it got stuck :P.