Add formula after index, match

[chunu]

Hi friends,

is it possible to add formula after index match in vba , in normal index match its working but not with vba.

vba code

Sub lookup()      lastrow = Cells(Rows.Count, "e").End(xlUp).Row
   For i = 2 To lastrow
      Range("e" & i) = Range("b" & Application.Match(Cells(i, 4), Range("a2:a" & lastrow), 0) + 1)
      Next
   End Sub

Normal index match

=INDEX(B10:B12,MATCH(D10,A10:A12,0))*G10-H10*G10

i want to add last part of normal index match (*G10-H10*G10)

Thanks

 

[Fluff]

How about

Range("e" & i) = Range("b" & Application.Match(Cells(i, 4), Range("a2:a" & LastRow), 0) + 1) * Range("G" & i) - Range("H" & i) * Range("G" & i)

 

[chunu]

Thank you so much

 

[Fluff]

You're welcome & thanks for the feedback

 

[chunu]

Hi,
sorry to bother you,
how to use this formula to get value from another workbook.

Thanks

How about

Range("e" & i) = Range("b" & Application.Match(Cells(i, 4), Range("a2:a" & LastRow), 0) + 1) * Range("G" & i) - Range("H" & i) * Range("G" & i)

 

[Fluff]

You would need to specify the workbook & worksheet with the ranges

 

[chunu]

You would need to specify the workbook & worksheet with the ranges

Thank you for your help, got the result.

Range("l" & i) = Application.Workbooks("data.xlsm").Worksheets("product").Range("e" & Application.Match(Cells(i, 6), Application.Workbooks("data.xlsm").Worksheets("product").Range("a2:a" & lastrow), 0) + 1) * Range("e" & i) - Range("j" & i) * Range("e" & i)

 

[Fluff]

You're welcome & thanks for the feedback