Look up and Auto Populate

September 14, 2010 at 07:54:41
Specs: Windows XP
Column A contains Equip Nos.
Column B Equip Descriptions (part of the Description may contain the Parent Equip No)
I want to search Column B to see if ANY of the Equip listed anywhere Column A is contained in the Description.

Column A --- Column B
RE503 --- REACTOR ON CE501
PU302 --- PUMP ON RE503

Results I would like to see.
Column C to reflect CE501 for line1.
Column C Blank for line 2.
Column C would show RE503 for line 3.

See More: Look up and Auto Populate

September 14, 2010 at 08:12:56
Thanks for letting us know what you want.

If you have any questions, feel free to ask.

Report •

September 14, 2010 at 15:06:14
Hi DerbyDad03, do you have any suggestions how I could solve this problem?
Any help greatly appreciated, its part of some data scrubbing so I can migrate data from maximo-4 to maximo-6.


Report •

September 14, 2010 at 19:10:21
Try this code.

I'm assuming the length of Column A and B is the same.

Sub DataScrub()
Dim lastRow, partNum As Integer
Dim c As Range
'Find last Row with data
 lastRow = Range("A" & Rows.Count).End(xlUp).Row
'Loop through Column A
    For partNum = 1 To lastRow
'Look for each part in Column B
        With Worksheets(1).Range("B1:B" & lastRow)
            Set c = .Find(Worksheets(1).Range("A" & partNum), _
                      LookIn:=xlValues, lookat:=xlPart)
'If part is found, place it in Column C and keep looking
'until all instances of that part is found
                If Not c Is Nothing Then
                    firstAddress = c.Address
                    Worksheets(1).Range("C" & c.Row) = _
                       Worksheets(1).Range("A" & partNum)
                    Set c = .FindNext(c)
                  Loop While Not c Is Nothing And _
                                 c.Address <> firstAddress
                End If
        End With
End Sub

Report •

Related Solutions

September 15, 2010 at 04:25:48
DerbyDad03 - excellent - worked 1st time, thanks for the help.


Report •

September 15, 2010 at 05:07:47
Glad I could help.

BTW the point of my first response was that we are all volunteers here, helping others because it's fun and challenging.

I assume that you wouldn't walk into a co-worker's office and simply blurt out what you want, expecting them to give it to you. I assume you would ask them for help.

It should be no different in an on-line forum. A "please and thank you" - upfront - goes a long away towards having people want to help you.

Report •

Ask Question