Index And Match Functions In Macro Code

Abdul Samad · Oct 24th 2018

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

Code

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

Display More

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
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 76"]C[/TD]
[TD="width: 73"]D[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 64"]F[/TD]
[TD="width: 64"]G[/TD]
[TD="width: 64"]H[/TD]

[/tr]

[tr]

[TD="align: right"]1[/TD]

[td]

Nov-Dec

[/td]

[td][/td]

[TD="width: 64"] [/TD]

[td]

1

[/td]

[td]

Jan-Feb

[/td]

[/tr]

[tr]

[TD="align: right"]2[/TD]

[td]

Nov-Dec

[/td]

[td][/td]

[td]

2

[/td]

[td]

Mar-Apr

[/td]

[/tr]

[tr]

[TD="align: right"]3[/TD]

[td]

Nov-Dec

[/td]

[td][/td]

[td]

3

[/td]

[td]

May-Jun

[/td]

[/tr]

[tr]

[TD="align: right"]4[/TD]

[td]

Nov-Dec

[/td]

[td][/td]

[td]

4

[/td]

[td]

Jul-Aug

[/td]

[/tr]

[tr]

[TD="align: right"]5[/TD]

[td]

Nov-Dec

[/td]

[td][/td]

[td]

5

[/td]

[td]

Sep-Oct

[/td]

[/tr]

[tr]

[TD="align: right"]6[/TD]

[td]

Nov-Dec

[/td]

[td][/td]

[td]

6

[/td]

[td]

Nov-Dec

[/td]

[/tr]

[/TABLE]

Thanks

**Ger Plante** · Oct 24th 2018

Hard to see what you are doing, because your sample table of data didnt copy/paste (misalignment) very well

Is this what you are trying to do?

Code

Sub Fill_Formula()


Dim rng As Range
Dim Lastrow As Long


With Sheet1


    Set rng = .Range("B1:B" & .Range("A" & Rows.Count).End(xlUp).Row) 'create the range of cells to take the formula.
    
    With Application
        rng.Formula = "=Index(H1:H6, Match(A1, G1:G6, 0), 1)" 'insert the formula into the range of cells.
        rng = rng.Value 'convert the formula to an actual value (comment this line out if you want to keep the cell formula there)
    End With
End With


End Sub

Display More

Ger

Abdul Samad · Oct 24th 2018

Thanks Ger Plantehttp://"https://www.ozgrid.com/forum/member/14138-ger-plante"

**alansidman** · Oct 24th 2018

Code Tags Added
Your post does not comply with our Forum RULES. Use code tags around code.

Posting code between

Code

tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window.

(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

Index And Match Functions In Macro Code

Participate now!

Share