On Friday, February 6, 2015 at 3:52:58 AM UTC-8, Claus Busch wrote:
Hi Howard,

Am Fri, 6 Feb 2015 03:05:39 -0800 (PST) schrieb L. Howard:

Trying to convert the Sub AP_by_State() to do the same as the event code.
It errors with a type mismatch as I have it now.

if you want to do it with an array try this way. Stepping through an
array is faster than stepping through the cells:

Sub AP_by_State2()
Dim LRow As Long
Dim varIn As Variant, varOut() As Variant
Dim i As Long, n As Long
Dim sAP As String

With Sheets("State AP")
sAP = ", " & .Range("B1")
LRow = .Cells(Rows.Count, "C").End(xlUp).Row
varIn = .Range("C1:C" & LRow)
For i = LBound(varIn) To UBound(varIn)
ReDim Preserve varOut(n)
If InStr(varIn(i, 1), sAP) Then
varOut(n) = varIn(i, 1)
n = n + 1
End If
Next
.Range("F1").Resize(rowsize:=n) = _
Application.Transpose(varOut)
End With
End Sub


Regards
Claus B.
--


I'll take these and give them a try and let you know how I made out.

Thanks.
Howard