Problème pour extraire une variable dans une formule

Bonjour,

j'ai la formule suivante:
=IF($B22=0," ",VLOOKUP($S22&$B$11&"Retail Sales Final"&" "&Ind!$CO$4,ZE0A!$A$1:$AR$626,Ind!I$43,FALSE))

Comment arriver à extraire "ze0a" automatiquement???

j'ai fait ça mais bon...=

y = ActiveCell.FormulaR1C1Local
y = Replace(y, "'", "")

totalLength = Len(y)
someVar = InStr(y, "Ind!")
theResult = Right(y, totalLength - someVar)
ca ne marche pas!!

merci à tous!!

Public Sub test_v2()
'the variables' declaration has been corrected, check
'the post's comments below to find out which correction were made
Dim lngLeft As Long, lngRight As Long, lngCommaPos As Long, lngptPos As Long
Dim i, bb As Integer
Range("A65000").Select
ActiveCell.End(xlUp).Offset(1, 0).Select


Row1 = ActiveCell.Row

For Each c In Worksheets("xxxx").Range("t14:t" & Row1 - 1).Cells

c.Select

' If Right(c.Value, 4) = "(MG)" Then



'If c.Value = "bibi" Or c.Value = "titi" Then

If c.Value <> "" Then


c.Select

j = ActiveCell.Row

y = ActiveCell.FormulaR1C1Local
y = Replace(y, "'", "")

totalLength = Len(y)
someVar = InStr(y, "Ind!")
theResult = Right(y, totalLength - someVar)


kk = Len(y)

lngCommaPos = InStr(1, y, "Ind!")
' lngptPos = InStr(1, y, ",")

y = Right(y, lngCommaPos)

lngptPos = InStr(1, y, ",")
aa = Mid(y, lngptPos + 5, 35)

cc = Range("b" & j).Value

If cc <> aa Then


Range(Selection, Selection.End(xlToRight)).Select

Selection.Replace What:=aa, Replacement:=cc, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End If

End If

Next


End Sub