Index And Match Functions In Macro Code

  • [h=2]Index And Match Functions In Macro Code[/h] 2 minutes ago
    Hi
    I want to write the following function using VBA code:


    =INDEX($H$1:$H$6,MATCH(A1,$G$1:$G$6,0))


    Code is given below


    Sub Fill_Formula()


    Dim rng As Range


    Dim sht As Worksheet


    Dim Lastrow As Long


    Dim Sk As Variant



    Set sht = Sheet1


    Lastrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row 'Finds lastrow for column A,update to whichever one you need


    Sk = Application.Index(Sheet1.Range("H1:H6"), Application.Match(Lastrow, Sheet1.Range("G1:G6"), 0), 1)



    Set rng = Range("B1:B" & Lastrow) 'Updating column C with formula, change to what you need


    rng.Formula = Sk


    End Sub




    After run above code I can get only H6 value in Col B for all rather than each corresponding value.I would appreciate to solve this problem

    Nov-Dec1Jan-Feb
    Nov-Dec2Mar-Apr
    Nov-Dec3May-Jun
    Nov-Dec4Jul-Aug
    Nov-Dec5Sep-Oct
    Nov-Dec6Nov-Dec



    Thanks