Option Explicit

Sub Als_Formel()
Const meineFORMEL As String = "=VLOOKUP(RC[XX],'YYY'!R2C1:R18C2,2,FALSE)"
Dim i As Integer
Dim x As String, y As String, Z As String

   For i = 1 To 2
      If i = 1 Then x = "ID1"
      If i = 1 Then y = "Information"
      If i = 1 Then Z = "first"
      If i = 2 Then x = "ID2"
      If i = 2 Then y = "Information2"
      If i = 2 Then Z = "Second"
   'gehe auf ID1 um X wert zu speichern

    Range("6:6").Find(x).Select
    Dim IDX As Integer
    IDX = ActiveCell.Column
    ActiveCell.Offset(1, 0).Value = IDX

    Range("6:6").Find(y).Select
    Dim IDY As Integer
    IDY = ActiveCell.Column
    ActiveCell.Offset(1, 0).Value = IDY
    
    Dim SK As Integer
    SK = IDX - IDY
    Dim strSK As String
    strSK = Format(SK, "#0")
    
    Dim strFormula As String
    Range("6:6").Find(y).Select
    ActiveCell.Offset(4, 0).Select
    strFormula = Replace(meineFORMEL, "XX", strSK)
    strFormula = Replace(strFormula, "YYY", x)
    ActiveCell.FormulaR1C1 = strFormula
   Next i

End Sub


'Alternativ
Sub Als_Wert()

Dim i As Integer
Dim x As String, y As String, Z As String

   For i = 1 To 2
      If i = 1 Then x = "ID1"
      If i = 1 Then y = "Information"
      If i = 1 Then Z = "first"
      If i = 2 Then x = "ID2"
      If i = 2 Then y = "Information2"
      If i = 2 Then Z = "Second"
   'gehe auf ID1 um X wert zu speichern

    Range("6:6").Find(x).Select
    Dim IDX As Integer
    IDX = ActiveCell.Column
    ActiveCell.Offset(1, 0).Value = IDX

    Range("6:6").Find(y).Select
    Dim IDY As Integer
    IDY = ActiveCell.Column
    ActiveCell.Offset(1, 0).Value = IDY
    
    Dim SK As Integer
    SK = IDX - IDY
    Dim strSK As String
    strSK = Format(SK, "#0")
    
    Range("6:6").Find(y).Select
    ActiveCell.Offset(4, 0).Select
    ActiveCell.Value = _
    WorksheetFunction.VLookup(ActiveCell.Offset(, SK), Sheets(x).Range("A2:B18"), 2)

   Next i

End Sub