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".

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