|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
Using multiple nested IFs formula seems okay for now, couldn't find a better way to do this yet. But my formula has 22 nested IFs so it's impossible to have to write it for each excel file that needs it.
That's why a VBA code in Personal.xlsb file would do:
Sub Diameter()
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
Columns("C").Insert Shift:=xlToRight
ActiveSheet.Range("C2").FormulaR1C1 = "=IF(SUMPRODUCT(--(ISNUMBER(SEARCH({""125""},RC[-1]))))>0,""125""," & _
"IF(SUMPRODUCT(--(ISNUMBER(SEARCH({""140""},RC[-1]))))>0,""140""," & _
"IF(SUMPRODUCT(--(ISNUMBER(SEARCH({""160""},RC[-1]))))>0,""160""," & _
"IF(SUMPRODUCT(--(ISNUMBER(SEARCH({""180""},RC[-1]))))>0,""180""," & _
"IF(SUMPRODUCT(--(ISNUMBER(SEARCH({""200""},RC[-1]))))>0,""200""," & _
"IF(SUMPRODUCT(--(ISNUMBER(SEARCH({""225""},RC[-1]))))>0,""225""," & _
"IF(SUMPRODUCT(--(ISNUMBER(SEARCH({""250""},RC[-1]))))>0,""250""," & _
"IF(SUMPRODUCT(--(ISNUMBER(SEARCH({""315""},RC[-1]))))>0,""315""," & _
"IF(SUMPRODUCT(--(ISNUMBER(SEARCH({""110""},RC[-1]))))>0,""110""," & _
"IF(SUMPRODUCT(--(ISNUMBER(SEARCH({""400""},RC[-1]))))>0,""400""," & _
"IF(SUMPRODUCT(--(ISNUMBER(SEARCH({""500""},RC[-1]))))>0,""500""," & _
"IF(SUMPRODUCT(--(ISNUMBER(SEARCH({""20""},RC[-1]))))>0,""20""," & _
"IF(SUMPRODUCT(--(ISNUMBER(SEARCH({""25""},RC[-1]))))>0,""25""," & _
"IF(SUMPRODUCT(--(ISNUMBER(SEARCH({""32""},RC[-1]))))>0,""32""," & _
"IF(SUMPRODUCT(--(ISNUMBER(SEARCH({""40""},RC[-1]))))>0,""40""," & _
"IF(SUMPRODUCT(--(ISNUMBER(SEARCH({""50""},RC[-1]))))>0,""50""," & _
"IF(SUMPRODUCT(--(ISNUMBER(SEARCH({""63""},RC[-1]))))>0,""63""," & _
"IF(SUMPRODUCT(--(ISNUMBER(SEARCH({""75""},RC[-1]))))>0,""75""," & _
"IF(SUMPRODUCT(--(ISNUMBER(SEARCH({""80""},RC[-1]))))>0,""80""," & _
"IF(SUMPRODUCT(--(ISNUMBER(SEARCH({""90""},RC[-1]))))>0,""90""," & _
"IF(SUMPRODUCT(--(ISNUMBER(SEARCH({""100""},RC[-1]))))>0,""100""," & _
"IF(SUMPRODUCT(--(ISNUMBER(SEARCH({""415""},RC[-1]))))>0,""415"",""""))))))))))))))))))))))"
Selection.Value = "Diametru"
ActiveSheet.Range("C2").Select
Selection.AutoFill destination:=Range("C2:C" & LastRow)
Columns("C:C").EntireColumn.AutoFit
Range("C1").Select
' REPLACE FORMULAS WITH VALUES, JUST IN CASE THIS NEEDS TO BE SENT TO THIRD PART IES
Dim MyRange As Range
Dim MyCell As Range
Set MyRange = Range("C2:C" & LastRow)
For Each MyCell In MyRange
If MyCell.HasFormula Then
MyCell.Formula = MyCell.Value
End If
Next MyCell
Rows("1:1").Select
Selection.AutoFilter
Application.ScreenUpdating = True
End Sub
|
Modificat de TRaP (acum 5 ani)
|
|
