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

 

[TitoElan]

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

OOMMG
I get the formula with

Sub RunThis()
    MsgBox ActiveCell.FormulaR1C1
End Sub

and now it works. Thanks Fluff, it was already a bad day because of vba.

 

[Fluff]

Glad to help & thanks for the feedback.