Searching for multiple occurances of a string

[fliss]

Help! I am a Java coder trying to pretend I can write VB! Unfortunately my deadline is coming quick and I am yet to figure this out!

I will try to make the question as simplified as I can.

I have an excel workbook with multiple worksheets. The first worksheet (we will call it Test1) has column A with a String value (this will be our search value). Column C should be populated by doing the following:
- Use the value of column A (Worksheet Test1) to search column B on the second worksheet (Worksheet Test2)
- I want to find each row in Test2 with the search value.
- For each of the resulting rows, Concatenate Column A (Test2) together to form a long string, comma seperated
- This string is what I want to be in Column C (Test1)

Does that make any sense? Can I do this? Is it even possible? (For all I know, it may be very easy!) Please help! I am getting quite desperate. I have been successful finding the first occurance, but getting all of the occurances has been a nightmare!

THANK YOU FOR ANY HELP!!!

 

[kevinclark]

There may be better ways but you can try this. It will allow you to search for numerous String values stored under column A and search through numerous Worksheets.
I have tried to comment it as much as possible on what
things you can change to make it suit your needs.

Sub Test()
Dim WS1 As String 'Worksheet example: Test1
Dim WS2 'Array of Worksheets to search
Dim strItem As String 'Item to search for
Dim i As Integer 'loop counter
Dim strTmp As String 'Temp String
Dim WS1row As Integer 'Worksheet 1 Row counter
Dim WS2row As Integer 'WorkSheet(ArrayIndex) Row Counter
Dim MaxRows As Integer 'Max number of rows to search

'The name of the sheet that has the item to be searched for
WS1 = "Tes1"
'Put the names of the sheets to be searched to this array
WS2 = Array("Test2", "Test3&quot
'This will search 500 rows for each sheet named in the array
MaxRows = 500

'From Row 1 to what ever limit you set (10 in this case)
For WS1row = 1 To 10
strItem = Sheets(WS1).Cells(WS1row, 1).Value
strTmp = ""
For i = 0 To UBound(WS2)
For WS2row = 1 To MaxRows
'Test for match ?
If Sheets(WS2(i)).Cells(WS2row, 2).Value = strItem Then
If strTmp <> &quot;&quot; Then
'add the comma to the string
strTmp = strTmp & &quot;, &quot; & Sheets(WS2(i)).Cells(WS2row, 1).Value
Else
'No comma
strTmp = Sheets(WS2(i)).Cells(WS2row, 1).Value
End If
End If
Next WS2row
Next i
'Write the values to column C AKA Column #3
Sheets(WS1).Cells(WS1row, 3).Value = strTmp
Next WS1row
End Sub