With a list of state/city in column A.

Wyoming - Greybull
Indiana - South Bend
Illinois - Itasca
Texas - Mineral Wells
Wyoming - Powell
Wyoming - Cody
Texas - Houston
Illinois - Fox Valley
Texas - Waco

I want the city returned to column D, where aState is a selection in
H1 DV drop down.

The code errors on the word FIND in the MID function.
The mid function works on the sheet as a formula. (with H1 cell ref,
not rngC)

Sub my_Instr_Copy()
Dim LRow As Long
Dim rngC As Range
Dim aState As String, aCity As String


With Sheets("Sheet1")

LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
aCity = .Cells(1, 8)
Did you mean State? (since you are searching for a city within a
state!)

For Each rngC In .Range("A2:A" & LRow)
Set aCity = Mid(rngC, Find("- ", rngC) + 2, 99)
You dim'd aCity as String. You are using it here as if it was dim'd an
object!

If InStr(rngC, aState) 0 Then
Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp)(2) = aCity
End If

Next

End With
End Sub

Thanks,
Howard

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus