.Formula

[vmjan02]

I have this formula, is there a way to fix it.

With Worksheets("Reference")
    With .Range("B2:B" & .Range("A" & .Rows.Count).End(xlUp).Row)
        '.Formula = "" 'Formula will be here
        .Value = .Value
     End With
End With

=IF(ISNUMBER(SEARCH("ALU-Nokia_Fiber_OLT",A2)),"1 Fiber Nokia OLT",IF(ISNUMBER(SEARCH("Huawei_Fiber_Device_Weekly",A2)),"2 Fiber Huawei OLT",IF(ISNUMBER(SEARCH("ALU-Nokia-FTTX_MDU-050.csv",A2)),"3 FTTX 050 Nokia MDU",IF(ISNUMBER(SEARCH("ALU-Nokia_Fiber_MDU_040-881G",A2)),"4 Fiber 040 Nokia MDU",IF(ISNUMBER(SEARCH("ALU-Nokia_Fiber_MDU_7356",A2)),"5 Fiber 7356 Nokia MDU",IF(ISNUMBER(SEARCH("Huawei_Fiber_Device_MDU_",A2)),"6 Fiber Huawei MDU",IF(ISNUMBER(SEARCH("HUA_5G_FTTM_Devices",A2)),"7 KPI Huawei MDU",IF(ISNUMBER(SEARCH("ALU-Nokia-FTTX_7360_GPON_Interfaces.csv",A2)),"8 Nokia FTTX 7360 GPON",IF(ISNUMBER(SEARCH("GPON_Interfaces_FWLT-B_Weekly",A2)),"9 Nokia FTTX 7360 GPON FWLT",IF(ISNUMBER(SEARCH("ALU-Nokia-FTTX_7360_GPON_Interfaces_FWLT-C (XGPON)_Weekly.csv",A2)),"10 Nokia FTTX 7360 GPON FWLT C XGPON",IF(ISNUMBER(SEARCH("ALU-Nokia-FTTX_MDU-050_UPLINK_If_Level_Weekly.csv",A2)),"11 ALU Nokia FTTX MDU-050 UPLINK",IF(ISNUMBER(SEARCH("ALU-Nokia_FiberMDU040-881G_GPONUplink_Weekly_Rep",A2)),"12 ALU Nokia FiberMDU040 881G GPONUplink",IF(ISNUMBER(SEARCH("ALU-Nokia_FiberMDU7356_GPONUplink_Weekly_Rep",A2)),"13 ALU Nokia FiberMDU7356 GPONUplink",IF(ISNUMBER(SEARCH("ALU-Nokia_FiberOLT7360_Uplink_Weekly_Rep",A2)),"14 ALU Nokia FiberOLT7360 Uplink",IF(ISNUMBER(SEARCH("Huawei_FiberMDU_GPONUplink_Weekly_Rep",A2)),"15 Huawei FiberMDU GPONUplink Weekly",IF(ISNUMBER(SEARCH("Huawei_FiberOLT_GPON_Weekly_Rep.csv",A2)),"16 Huawei FiberOLT GPON Weekly",IF(ISNUMBER(SEARCH("Huawei_FiberOLT_Uplink_Weekly_Rep.csv",A2)),"17 Huawei FiberOLT Uplink Weekly",IF(ISNUMBER(SEARCH("HUA_5G_MDU_Uplink_Weekly",A2)),"18 HUA 5G MDU Uplink Weekly",IF(ISNUMBER(SEARCH("Nokia OLT_Uplink interface 40per",A2)),"19 Nokia OLT Uplink interface 40per",IF(ISNUMBER(SEARCH("OLT_Uplink interface 40per.csv",A2)),"20 OLT Uplink interface 40per"))))))))))))))))))))

 

[RoryA]

Since your formula contains quoted text, you'll need to double all the quotes within the formula when you use it in code.

 

[vmjan02]

yes true, but still no luck

 

[RoryA]

What does that mean exactly? what did you try (specifically) and what happened?

 

[Georgiboy]

Your formula is rather large so probably best to split it into segments as below:

Sub test()
    With Worksheets("Reference")
        With .Range("B2:B" & .Range("A" & .Rows.Count).End(xlUp).Row)
            .Formula = "=IF(ISNUMBER(SEARCH(""ALU-Nokia_Fiber_OLT"",A2)),""1 Fiber Nokia OLT""," & _
                        "IF(ISNUMBER(SEARCH(""Huawei_Fiber_Device_Weekly"",A2)),""2 Fiber Huawei OLT""," & _
                        "IF(ISNUMBER(SEARCH(""ALU-Nokia-FTTX_MDU-050.csv"",A2)),""3 FTTX 050 Nokia MDU""," & _
                        "IF(ISNUMBER(SEARCH(""ALU-Nokia_Fiber_MDU_040-881G"",A2)),""4 Fiber 040 Nokia MDU""," & _
                        "IF(ISNUMBER(SEARCH(""ALU-Nokia_Fiber_MDU_7356"",A2)),""5 Fiber 7356 Nokia MDU""," & _
                        "IF(ISNUMBER(SEARCH(""Huawei_Fiber_Device_MDU_"",A2)),""6 Fiber Huawei MDU""," & _
                        "IF(ISNUMBER(SEARCH(""HUA_5G_FTTM_Devices"",A2)),""7 KPI Huawei MDU""," & _
                        "IF(ISNUMBER(SEARCH(""ALU-Nokia-FTTX_7360_GPON_Interfaces.csv"",A2)),""8 Nokia FTTX 7360 GPON""," & _
                        "IF(ISNUMBER(SEARCH(""GPON_Interfaces_FWLT-B_Weekly"",A2)),""9 Nokia FTTX 7360 GPON FWLT""," & _
                        "IF(ISNUMBER(SEARCH(""ALU-Nokia-FTTX_7360_GPON_Interfaces_FWLT-C (XGPON)_Weekly.csv"",A2)),""10 Nokia FTTX 7360 GPON FWLT C XGPON""," & _
                        "IF(ISNUMBER(SEARCH(""ALU-Nokia-FTTX_MDU-050_UPLINK_If_Level_Weekly.csv"",A2)),""11 ALU Nokia FTTX MDU-050 UPLINK""," & _
                        "IF(ISNUMBER(SEARCH(""ALU-Nokia_FiberMDU040-881G_GPONUplink_Weekly_Rep"",A2)),""12 ALU Nokia FiberMDU040 881G GPONUplink""," & _
                        "IF(ISNUMBER(SEARCH(""ALU-Nokia_FiberMDU7356_GPONUplink_Weekly_Rep"",A2)),""13 ALU Nokia FiberMDU7356 GPONUplink""," & _
                        "IF(ISNUMBER(SEARCH(""ALU-Nokia_FiberOLT7360_Uplink_Weekly_Rep"",A2)),""14 ALU Nokia FiberOLT7360 Uplink""," & _
                        "IF(ISNUMBER(SEARCH(""Huawei_FiberMDU_GPONUplink_Weekly_Rep"",A2)),""15 Huawei FiberMDU GPONUplink Weekly""," & _
                        "IF(ISNUMBER(SEARCH(""Huawei_FiberOLT_GPON_Weekly_Rep.csv"",A2)),""16 Huawei FiberOLT GPON Weekly""," & _
                        "IF(ISNUMBER(SEARCH(""Huawei_FiberOLT_Uplink_Weekly_Rep.csv"",A2)),""17 Huawei FiberOLT Uplink Weekly""," & _
                        "IF(ISNUMBER(SEARCH(""HUA_5G_MDU_Uplink_Weekly"",A2)),""18 HUA 5G MDU Uplink Weekly""," & _
                        "IF(ISNUMBER(SEARCH(""Nokia OLT_Uplink interface 40per"",A2)),""19 Nokia OLT Uplink interface 40per""," & _
                        "IF(ISNUMBER(SEARCH(""OLT_Uplink interface 40per.csv"",A2)),""20 OLT Uplink interface 40per""))))))))))))))))))))"
            .Value = .Value
         End With
    End With
End Sub

Another option could be to rewrite the formula into a sub, could be done better than the below but for reference:

Sub test2()
    Dim rCell As Range

    With Worksheets("Reference")
        For Each rCell In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row).Cells
            If InStr(rCell.Value, "ALU-Nokia_Fiber_OLT") Then rCell.Offset(, 1) = "1 Fiber Nokia OLT"
            If InStr(rCell.Value, "Huawei_Fiber_Device_Weekly") Then rCell.Offset(, 1) = "2 Fiber Huawei OLT"
            'etc...
            'etc...
            'etc...
        Next rCell
    End With
End Sub

 

[vmjan02]

ok sure will give it a try.