# Converting a Search Function into a VBA equation • ### 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

• 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