Search through non-distinctive data

abautwag · #1 04-08-2014, 11:49 AM

Is it possible to search through non-distinctive data using VBA?

For example:

Worksheet "A"

Column A
A1
A2
A3
A4
A5

Worksheet "B":
Column A | Column B | Column C
1/1/2014 | A1 | COMPLETE
1/2/2014 | A2 | STARTED
1/3/2014 | A3 | STARTED

1/4/2014 | A4 | COMPLETE
1/5/2014 | A3 | COMPLETE

I want to be able to search through Worksheet "B" for every value listed in Worksheet "A". If the value is found, I want the information listed in each row from Worksheet "B" into Worksheet "C". In this case, I should have 2 rows of information for "A1". I haven't started on the code yet but I am getting lost in the logic with creating a loop to continue to search for "A1" until all values have been found, copied, and pasted, and then search for A2, A3.. etc.

Is this possible to do?

Thanks.

macropod · #2 04-08-2014, 05:27 PM

Yes, it's quite possible, but I don't see why you'd expect two rows on Sheet C for 'A1', when that value only appears once on Sheet B.

abautwag · #3 04-09-2014, 07:43 AM

My mistake - I meant to write A3. But the case is all the same. How can this be done?

macropod · #4 04-09-2014, 04:42 PM

Try:

Code:

Sub Demo()
Application.ScreenUpdating = False
Dim xlWkSht As Worksheet, ArrData As String, StrMatch
Dim LRow As Long, i As Long, j As Long, k As Long
With ThisWorkbook
  Set xlWkSht = .Worksheets("Sheet3")
  k = xlWkSht.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row
  If k = 1 Then
    With .Worksheets("Sheet2").UsedRange
      xlWkSht.Cells(k, 1) = .Cells(1, 1)
      xlWkSht.Cells(k, 2) = .Cells(1, 2)
      xlWkSht.Cells(k, 3) = .Cells(1, 3)
    End With
  End If
  With .Worksheets("Sheet1").UsedRange
    LRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
    For i = 1 To LRow
      ArrData = ArrData & .Cells(i, 1).Text & "|"
    Next
  End With
  With .Worksheets("Sheet2").UsedRange
    LRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
    For i = 0 To UBound(Split(ArrData, "|"))
      StrMatch = Split(ArrData, "|")(i)
      For j = 1 To LRow
        If .Cells(j, 2).Text = StrMatch Then
          k = k + 1
          xlWkSht.Cells(k, 1) = .Cells(j, 1)
          xlWkSht.Cells(k, 2) = .Cells(j, 2)
          xlWkSht.Cells(k, 3) = .Cells(j, 3)
        End If
      Next
    Next
  End With
End With
Set xlWkSht = Nothing
Application.ScreenUpdating = True
End Sub

Change my "Sheet1", "Sheet2" & "Sheet3" references to whatever you actually use - I assume it's not really "A", "B" & "C".

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Search for date and then apply mutliple search criteria in huge dataset	maxtymo	Excel	2	12-01-2013 04:52 AM
Looking for Windows Search app with ability to search by content	gopher_everett	Office	1	02-28-2013 09:23 PM
Data table search function	omtinole	Excel Programming	1	07-13-2012 10:03 PM
Using data entered in prompt to search database table	BluRay	Word	1	03-25-2011 01:47 AM
Search and Replace - Clear Search box	JostClan	Word	1	05-04-2010 08:46 PM