Answered by:
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
Answers

It is unclear what should go into cell B1, but this does your formulas:
Sub TestMacro()
Dim myR As LongmyR = 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
 Edited by Bernie Deitrick, Excel MVP 20002010 Monday, February 27, 2012 7:12 PM
 Marked as answer by Hopeful Learner Monday, February 27, 2012 8:32 PM
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 LongmyR = 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
 Edited by Bernie Deitrick, Excel MVP 20002010 Monday, February 27, 2012 7:12 PM
 Marked as answer by Hopeful Learner Monday, February 27, 2012 8:32 PM
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
 Edited by Bernie Deitrick, Excel MVP 20002010 Monday, February 27, 2012 8:28 PM
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).ValueMonday, February 27, 2012 8:33 PM