AW: Teil von String löschen, wenn Bedingung erfüllt
25.02.2019 11:23:14
String
Hi
als reines VBA so:
Sub test1()
Dim arr1, arr2
Dim rng2 As Range
Dim z As Long
With ActiveSheet.UsedRange
arr1 = .Columns(1).Value
Set rng2 = .Columns(4)
arr2 = rng2.Value
End With
For z = 3 To UBound(arr1, 1)
If arr1(z, 1) Like "Y00[13]" Then
If Len(arr2(z, 1)) > 8 Then arr2(z, 1) = Left(arr2(z, 1), Len(arr2(z, 1)) - 8)
End If
Next
rng2.Value = arr2
End Sub
als Formel-VBA-Lösung so
Sub test2()
With Range(Cells(3, 1), Cells.SpecialCells(xlCellTypeLastCell))
With .Columns(.Columns.Count + 1)
.FormulaR1C1 = _
"=IF(OR(RC1=""Y001"",RC1=""Y003""),If(Len(RC4)>8,Left(RC4,Len(RC4)-8),RC4),RC4)"
.Copy
.Offset(0, 4 - .Column).PasteSpecial xlPasteValues
.ClearContents
End With
End With
End Sub
Gruß Daniel