I have created a module and it works for the task i require, so far. The thing is that when i change the formula that is to be inserted to a cell, the error 1004 appears, and claims application/object defined error.

here is the line that i change

If fgh = 2 Then
Sheets("sheet2").Cells(fgh, 2).formula = "=index(sheet1!b:b,match(c2,sheet1!A:A,0),1))"
Else
to
If fgh = 2 Then
Sheets("sheet2").Cells(fgh, 2).formula = "=if(c2="",index(sheet1!b:b,match(d2,sheet1!A:A,0),1),index(sheet1!b:b,match(c2,sheet1!A:A,0),1))"
Else
The only differnce is that i ask it to check for a blank cell, and use the correct formula. I would use VB to do this, but it needs to be able to use the code for several iterates of "fgh" which then changes the values of "d2" and "c2" to =fgh. The way i was to do this was autofill the cells from the above formula.

Any ideas appreciated.