Array coding type mismatch
Hi Howard,
Am Fri, 6 Feb 2015 03:05:39 -0800 (PST) schrieb L. Howard:
Abilene, TX (ABI)
Abilene, TX (ABI)
Adak Island, AK (ADK)
Akiachak, AK (KKI)
Akiak, AK (AKI)
Akron/Canton, OH (CAK)
Akuton, AK (KQA)
Alakanuk, AK (AUK)
Alamogordo, NM (ALM)X
Alamosa, CO (ALS)
try it with autofilter. This way is more reliable and faster:
Sub AP_by_State()
Dim LRow As Long
With Sheets("State AP")
LRow = .Cells(Rows.Count, "C").End(xlUp).Row
.Columns("C").AutoFilter Field:=1, _
Criteria1:="*, " & .Range("B1") & "*"
.Range("C2:C" & LRow).Copy .Range("F1")
.Range("C2:C" & LRow).SpecialCells(xlCellTypeVisible) _
.Interior.ColorIndex = 19
.AutoFilterMode = False
End With
End Sub
Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) < "B1" Or Target.Count 1 Then Exit Sub
Range("C:C").Interior.ColorIndex = xlNone
Range("F:F").ClearContents
Call AP_by_State
End Sub
Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
|