1

I have some data that contains both numeric and string values

Name Code 

Bob   1

Alice 2

Bob   33AL

Dan   AFAL

Alert  AL99

Bob    ALP

I want to select names where the "Code" column contains "AL" somewhere.

If I do 

 If InStr(0, Cells(j, Code_column).Value, "AL", vbTextCompare) <> 0 Then

then my loop doesn't run because of the numeric values at some points in the column.

I have tried 

 If InStr(0, CStr(Cells(j, Code_column).Value), "AL", vbTextCompare) <> 0 Then

but I am getting the same error. What should I try? A pre-loop checking if it is numeric?

Community
  • 1
  • 1
Amatya
  • 1,203
  • 6
  • 32
  • 52
  • 1
    `If InStr(1, Cells(j, Code_column).Value, "AL", vbTextCompare) <> 0 Then` works for me. – Siddharth Rout Dec 09 '13 at 01:05
  • Use an AutoFilter to do this and avoid looping. – brettdj Dec 09 '13 at 01:07
  • @brettdj can you tell me how to do that? Incidentally, I am looping to create an array so I could later autofilter copy and paste. I need to split the data in 5 ways. If it contains "AL" then separate, if not if it satisfies criteria 2 then separate, if not.... and so on sequentially across different criteria. So not only do I need to filter records that have "AL" , I need to keep track of records that don't have "AL" for later filtering. – Amatya Dec 09 '13 at 01:15
  • @SiddharthRout I am gonna try staring at 1 and tell you what happened. – Amatya Dec 09 '13 at 01:16
  • 1
    If you want to follow the autofilter example as @brettdj suggested then this will get you started http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s – Siddharth Rout Dec 09 '13 at 01:22
  • 1
    @SiddharthRout your code is working, thanks! – Amatya Dec 09 '13 at 01:34

1 Answers1

2

In code something like this for data in Columns A:B of the active sheet

You can do the same thing manually without code

Then in both cases work with the output (plus then reverse the logic for your second criteria)

Sub CookHooker()
Dim rng As Range
ActiveSheet.AutoFilterMode = False
Set rng1 = Range([a1], Cells(Rows.Count, "B").End(xlUp))
rng1.AutoFilter Field:=2, Criteria1:="=*AL*", Operator:=xlAnd
End Sub
brettdj
  • 54,857
  • 16
  • 114
  • 177