[Solved] VBA Vlookup and complex Formula problem

Hi all,
Im trying to turn this formula:

=MID(VLOOKUP(B103;'Extracto Siebel'!$1:$1048576;5;FALSE);SEARCH("113";VLOOKUP(B103;'Extracto Siebel'!$1:$1048576;5;FALSE);1);13)

Into something i can put in a macro so it goes automatically in a range with a macro.

My problem is that "B103" is a variable and refers to 3 cells to the left of where the cell is.

So. i used this:

Dim rngCell As Range
       For Each rngCell In Range("B" & ActiveCell.Row & ":B" & (ActiveCell.Row + count))
    rngCell.Offset(0, 3) = Application.WorksheetFunction.Mid((Application.WorksheetFunction.VLookup(rngCell.Value, Sheets("Extracto Siebel").Range("1:1048576"), "5", False)), Application.WorksheetFunction.Search("113", Application.WorksheetFunction.VLookup(rngCell.Value, Sheets("Extracto Siebel").Range("1:1048576"), "5", False), 1), 13)
    
    Next

But It does not work.

This one works, but fills everything up with a Fixed reference: "$B$103"

Range("E" & ActiveCell.Row, "E" & (ActiveCell.Row + count)).Formula = "=MID(VLOOKUP(" & ActiveCell.Offset(0, -3).AddressLocal & ",'Extracto Siebel'!$1:$1048576,5,FALSE),SEARCH(""113"",VLOOKUP(" & ActiveCell.Offset(0, -3).AddressLocal & ",'Extracto Siebel'!$1:$1048576,5,FALSE),1),13)"

Any way to do it??

Thanks in Advance!!!!

Hi,
Try using FormulaR1C1 instead. So you can use relative and/or absolute references.

Go in the cell where your formula is, then open the VBE editor immediate windows and type : Debug.Print ActiveCell.formulaR1c1
Use this result in your code :
Example :

rngcell.formulaR1C1 = " the formula here... "

Try:

rngcell.FormulaR1C1="=MID(VLOOKUP(RC[-3];'Extracto Siebel'!R1:R1048576;5;FALSE);SEARCH(""113"";VLOOKUP(RC[-3];'Extracto Siebel'!R1:R1048576;5;FALSE);1);13)"