Find, and replace with offset a cell

[TitoElan]

Hi there. I did one macro to find a value, then change a cell's value with an offset. Just a string. It is working. Now I just wanted to use the same macro, but I dont want to replace the content from the offset matching cells with a string but with a formula. And I have tried for hours to find a solution without success. Maybe I can get some help here. I would appreciate it. The code (last try) looks like this
That code finds well the cells, but without a relative formula and I need It.
If I try to use myFormula = "=IF(RC[-9]>0,IF(RC[-8]=1,VLOOKUP(RC[-9],matpreis!R1C1:R2567C7,3,FALSE),VLOOKUP(RC[-9],matpreis!R1C1:R2567C7,2,FALSE)),"""")" as formula nothing happends. Kinda sad. It is like almost done but I cant get it working.

Sub Schaltfläche4_Klicken()
Dim find_value, replace_value As String
Dim i As Long
Dim myFormula As String
Dim Rng As Range
Dim Rng_2 As Range
Dim c As Range

On Error Resume Next

myFormula = "=IF(RC[-9]>0,IF(RC[-8]=1,VLOOKUP(RC[-9],matpreis!R1C1:R2567C7,3,FALSE),VLOOKUP(RC[-9],matpreis!R1C1:R2567C7,2,FALSE)),"""")"
find_value = InputBox("Wählen Sie den Profiltyp zB. QRR oder ROHR...")

 

i = 0
For Each Rng In Worksheets("Tabelle1").Range("E6:E28")
 
    If Rng.Value Like "*" & find_value & "*" Then
        Rng.Offset(0, 9).Formula = "=WENN(E17>0;WENN(F17=1;SVERWEIS(E17;matpreis!$A$1:$G$2567;3;FALSCH);SVERWEIS(E17;matpreis!$A$1:$G$2567;2;FALSCH));"""")": i = i + 1
        
    End If


        '=WENN(E17>0;WENN(F17=1;SVERWEIS(E17;matpreis!$A$1:$G$2567;3;FALSCH);SVERWEIS(E17;matpreis!$A$1:$G$2567;2;FALSCH));"""")
        '=IF(RC[-9]>0,IF(RC[-8]=1,VLOOKUP(RC[-9],matpreis!R1C1:R2567C7,3,FALSE),VLOOKUP(RC[-9],matpreis!R1C1:R2567C7,2,FALSE)),"""")
    

Next

If i = 1 Then
MsgBox i & " Zelle geändert"
ElseIf i > 1 Then
MsgBox i & " Zellen geändert"
Else
MsgBox "Keine Zellen geändert"
End If



End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

        Rng.Offset(0, 9).FormulaR1C1 = myFormula

 

[TitoElan]

Thank you very much for your answer. I have this variable because I already tried that. Nothing happens
But if I use the

Rng.Offset(0, 9).FormulaR1C1 = myFormula

And I delete the "=" from the formula like this

myFormula = "IF(RC[-9]>0,IF(RC[-8]=1,VLOOKUP(RC[-9],matpreis!R1C1:R2567C7,3,FALSE),VLOOKUP(RC[-9],matpreis!R1C1:R2567C7,2,FALSE)),"""")"

It works like a string, but at least it does something. I think the problem with the code is that Excel can't find the cells with the VBA structure.

 

[Fluff]

If you remove this line On Error Resume Next do you get any error messages?

 

[TitoElan]

If you remove this line On Error Resume Next do you get any error messages?

Error 1004 - application defined or object defined Error
At this point

Rng.Offset(0, 9).FormulaR1C1 = myFormula

 

[Fluff]

What happens if you use

myFormula = "=WENN(E17>0;WENN(F17=1;SVERWEIS(E17;matpreis!$A$1:$G$2567;3;FALSCH);SVERWEIS(E17;matpreis!$A$1:$G$2567;2;FALSCH));"""")"

 

[TitoElan]

What happens if you use

myFormula = "=WENN(E17>0;WENN(F17=1;SVERWEIS(E17;matpreis!$A$1:$G$2567;3;FALSCH);SVERWEIS(E17;matpreis!$A$1:$G$2567;2;FALSCH));"""")"

The code correctly finds the cells to change and writes the formula but as an absolute formula. Without errors

 

[Fluff]

In that case just that formula so it uses r1c1 references.

 

[TitoElan]

If I use that formula, how can I make it as relative formula?

 

[Fluff]

Just change the range references from A1 notation to R1C1 notation as you've done for the English version of the formula.