Index match VBA

[anand3dinesh]

Hi i'm a beginner can you please help me to wright VBA script for "Index match" that returns value from other worksheet?
please refer images i have 2 worksheets a and B in sheet b i need code that fills cells according to the column heading

 

[VBE313]

assuming your column headers are in A1:D1, why cant you just go to Sheet B in cell B2 and put =VLOOKUP(A2,'sheet a'!$A$2:$D$11,2,FALSE) ?

 

[anand3dinesh]

i know Vlookup and Index Match functions, i am trying to learn VBA Script

assuming your column headers are in A1:D1, why cant you just go to Sheet B in cell B2 and put =VLOOKUP(A2,'sheet a'!$A$2:$D$11,2,FALSE) ?

 

[anand3dinesh]

i know Vlookup and Index Match functions, i am trying to learn VBA Script

thank you for replying BTW, it would be very helpful if you share VBA code for that- Cheers

 

[VBE313]

thank you for replying BTW, it would be very helpful if you share VBA code for that- Cheers

why would you need to use VBA for that?

 

[Fluff]

How about

Sub anand3dinesh()
    With Sheets("Sheet2")
        With .Range("B2:B" & .Range("A" & Rows.Count).End(xlUp).Row)
            .Formula = "=INDEX(Sheet1!$B$2:$D$9,MATCH($A2,Sheet1!$A$2:$A$9,0),MATCH(B$1,Sheet1!$B$1:$D$1,0))"
            .Value = .Value
        End With
    End With
End Sub

change sheet names in red to suit.
Remove the line in blue if you want to keep the formula

 

[anand3dinesh]

why would you need to use VBA for that?

im trying to learn VBA

 

[anand3dinesh]

How about

Sub anand3dinesh()
    With Sheets("Sheet2")
        With .Range("B2:B" & .Range("A" & Rows.Count).End(xlUp).Row)
            .Formula = "=INDEX(Sheet1!$B$2:$D$9,MATCH($A2,Sheet1!$A$2:$A$9,0),MATCH(B$1,Sheet1!$B$1:$D$1,0))"
            .Value = .Value
        End With
    End With
End Sub

change sheet names in red to suit.
Remove the line in blue if you want to keep the formula

Hi thank you for the code, when i run i throw Run Time error.
it says Subscript out of range

 

[Fluff]

Did you change the sheet names?

 

[anand3dinesh]

Did you change the sheet names?

yeah that worked, thank you so much for helping