Problème pour extraire une variable dans une formule
pseudo_polo
-
eriiic Messages postés 24581 Date d'inscription Statut Contributeur Dernière intervention -
eriiic Messages postés 24581 Date d'inscription Statut Contributeur Dernière intervention -
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
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
2 réponses
-
Bonsoir,
Comment arriver à extraire "ze0a" automatiquement???
f = Split(Split(formule, ",")(3), "!")(0)
eric -
Merci beaucoup Eric, c'est sympa!!
Par contre, imaginons que j'ai des #N/A ou des #ref! dans une formule que ma macro est censée lire, alors ma macro s'arrête alors qu'elle devrait remplacer "ref par exemple par bibi par exemple.
Merci et bonne journée!
Mon code =
Public Sub test_v2()
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("t13:t" & Row1 - 1).Cells
c.Select
If c.Value <> "" Then
'Or IsError(c.Value)
c.Select
j = ActiveCell.Row
y = ActiveCell.FormulaR1C1Local
y = Replace(y, "'", "")
y = Split(Split(y, ",")(3), "!")(0)
If Left(Range("b" & j).Value, 2) = "MG" Then
cc = Right(Range("b" & j).Value, 4)
If cc <> y Then
Rows(j & ":" & j).Select
Selection.Replace What:=aa, Replacement:=cc, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If
Else:
cc = Range("b" & j).Value
If cc <> y Then
Rows(j & ":" & j).Select
Selection.Replace What:=aa, Replacement:=cc, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If
End If
End If
Next
End Sub
