Find String within Cell

[Guest]

I have a spreadsheet which uses concatenated data as the basis for sumif
calculations looking at another spreadsheet. In separate columns however, I
want to be able to split out the concatenated data.

Column AM has my concatenated data as follows:

Glasgow Property Freehold
South Yorkshire Property Shorthold Lease
Edinburgh Other Telephone System

I want to be able to search in column AM, find the Region (Glasgow,
Edinburgh....) and return that region into column B of the same row. I have
approx 20 regions that I am looking for which are listed in a range called
"Opcos"

I think it's vbTextCompare I need to use but don't know where to start with
writing the code to do this. Could someone please help me with the code

 

[Guest]

Try this


Sub ExtractLocation()


LastrowF = Cells(Rows.Count, "F").End(xlUp).Row
Set Opcos = Range(Cells(1, "F"), Cells(LastrowF, "F"))

LastrowAM = Cells(Rows.Count, "AM").End(xlUp).Row
Set AMRange = Range(Cells(1, "AM"), Cells(LastrowAM, "AM"))


For Each cell In Opcos

Set c = AMRange.Find(cell, LookIn:=xlValues)
If Not c Is Nothing Then
Cells(cell.Row, "B") = cell

End If

Next cell

End Sub

 

[Bernie Deitrick]

KAren,

Sub FindOpCos()

Dim myAM As Range
Dim myCell As Range

For Each myAM In Range("AM1", Range("AM65536").End(xlUp))
For Each myCell In Range("Opcos")
If InStr(1, myAM.Value, myCell.Value) > 0 Then
Cells(myAM.Row, 2).Value = myCell.Value
GoTo Found:
End If
Next myCell
Found:
Next myAM

End Sub

HTH,
Bernie
MS Excel MVP

 

[Guest]

It runs but doesn't do what I want it to. Should I be replacing the "f" with
something?

 

[Guest]

ry changing this line

from:
Cells(cell.Row, "B") = cell
to:
Cells(c.Row, "B") = cell

 

[Bernie Deitrick]

Karen,

Joel's method is also written to only find each Opcos value once.... I think mine will do what you
want.

HTH,
Bernie
MS Excel MVP