locked
Converting a Search Function into a VBA equation RRS feed

  • Question

  • I am looking to change the following two equations into a VBA macro:

    output in column A: IFERROR(IF(SEARCH("L",C11,1)=1,C11,""),"")

    and

    output in column B: IFERROR(IF(C12>0.0001,IF(B11="",A11,B11),""),"")

    I need these equations to run from the top of the column all the way down to the last line of data in column C. There are breaks in the data in column C as well so just because there is a space in Column C, doesn't mean it is the end of the data in Column C.

    This is what the cells look like using the functions above and what I want it to look like with the macro.

            A           B         C

    1   L0001              L0001

    2               L0001  120

    3.              L0001   2846

    4.              L0001   5668

    5.

    6.                           * L0

    7.   L0002              L0002

    8.               L0002   875

    9.

    10.                         * L0

    I'm not very good at VBA yet so if you can help me out, that would be amazing.

    Monday, February 27, 2012 6:41 PM

Answers

  • It is unclear what should go into cell B1, but this does your formulas:

    Sub TestMacro()
    Dim myR As Long

    myR = Cells(Rows.Count, 3).End(xlUp).Row
    Range("A1:A" & myR).Formula = "=IFERROR(IF(SEARCH(""L"",C1,1)=1,C1,""""),"""")"
    Range("B2:B" & myR).Formula = "=IFERROR(IF(C1>0.0001,IF(B1="""",A1,B1),""""),"""")"
    Range("A1:B" & myR).Value = Range("A1:B" & myR).Value
         
    End Sub


    HTH, Bernie


    Monday, February 27, 2012 7:12 PM

All replies

  • It is unclear what should go into cell B1, but this does your formulas:

    Sub TestMacro()
    Dim myR As Long

    myR = Cells(Rows.Count, 3).End(xlUp).Row
    Range("A1:A" & myR).Formula = "=IFERROR(IF(SEARCH(""L"",C1,1)=1,C1,""""),"""")"
    Range("B2:B" & myR).Formula = "=IFERROR(IF(C1>0.0001,IF(B1="""",A1,B1),""""),"""")"
    Range("A1:B" & myR).Value = Range("A1:B" & myR).Value
         
    End Sub


    HTH, Bernie


    Monday, February 27, 2012 7:12 PM
  • Dernie Deitrick,

    Thank you for your response. It is very close to working, the only thing that isn't working is that for example in cell B5, it inserts "L0001". What change can be made to make sure that when there is a blank in Column C, there is nothing inserted into Column B?

    Monday, February 27, 2012 7:42 PM
  • Sorry - I was off by one row (I think)

    Try changing

    Range("B2:B" & myR).Formula = "=IFERROR(IF(C1>0.0001,IF(B1="""",A1,B1),""""),"""")"

    to

    either

    Range("B2:B" & myR).Formula = "=IFERROR(IF(C2>0.0001,IF(B1="""",A1,B1),""""),"""")"

    or

    Range("B2:B" & myR).Formula = "=IF(C2="""","""",IFERROR(IF(C1>0.0001,IF(B1="""",A1,B1),""""),""""))"


    HTH, Bernie

    Monday, February 27, 2012 8:26 PM
  • I figured it out. Thank you.
    Monday, February 27, 2012 8:32 PM
  • Just so you know it is

    Dim myR As Long

    myR = Cells(Rows.Count, 3).End(xlUp).Row
    Range("A1:A" & myR).Formula = "=IFERROR(IF(SEARCH(""L"",C1,1)=1,C1,""""),"""")"
    Range("B2:B" & myR).Formula = "=IFERROR(IF(C2>0.0001,IF(B1="""",A1,B1),""""),"""")"
    Range("A1:B" & myR).Value = Range("A1:B" & myR).Value

    Monday, February 27, 2012 8:33 PM