Solved: VBA to find a value and copy values from sheet2

[richardSmith]

I have worksheet1 which contains the ComputerNameListed and worksheet2 which contains the user information. What I want to do is search worksheet1 for each instance of ComputerNameListed (it always starts with ComputerNameListed - (Actual name here) and when ComputerNameListed is found go to worksheet2 and find in column A the matching PC Name.

Part 2, I then need to copy the ROW from worksheet2 with the matching ComputerNameListed over to worksheet1, but past all the values from the ROW into one cell.

I was putting together some VBA to perform this, but realize I am out of my league! I was working on the find function which I have something like this:

Set c = .Find("ComputerNameListed*", lookin:=XlValues)


How would one perform these tasks in vba?

[shrivallabha]

Hi,

Test this code (be sure to read comments and edit if needed)

Public Sub CopyAndCombineData()
Dim c As Range, r As Range
Dim i As Long

Dim strvalue As String

Application.ScreenUpdating = False

'I have assumed PC Name resides in column A of Sheet1. Change to suit
For Each c In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)

'Sheet 2 name is assumed as "Sheet2". Change it to suit
Set r = Sheets("Sheet2").Range("A:A").Find(c.Value, [A1], xlValues, xlWhole, _
xlByRows, xlNext, True)

If Not r Is Nothing Then
strvalue = vbNullString
For i = 2 To Sheets("Sheet2").Cells(r.Row, Columns.Count).End(xlToLeft).Column
strvalue = strvalue & " " & Sheets("Sheet2").Cells(r.Row, i).Value
Next i
c.Offset(0, 1).Value = Trim(strvalue)
End If

Next c

Application.ScreenUpdating = True

End Sub

[richardSmith]

Your assumptions are all correct :)!!

What is the piece that tells the code what search string to be looking for?

[shrivallabha]

This line:
'Sheet 2 name is assumed as "Sheet2". Change it to suit
Set r = Sheets("Sheet2").Range("A:A").Find(c.Value, [A1], xlValues, xlWhole, _
xlByRows, xlNext, True)

The bold part refers to Sheet1 PC Name and the rest are various option in Find method.

[richardSmith]

A few quick tweaks and I'll be golden. I need it to search for PCName* as there may be numerics or alpha-numeric combinations after this.

Also, the search range is from A1 - Z300.

Thanks again !!!

[shrivallabha]

If you are looking for partial match then change this:
'Sheet 2 name is assumed as "Sheet2". Change it to suit
Set r = Sheets("Sheet2").Range("A:A").Find(c.Value, [A1], xlValues, xlWhole, _
xlByRows, xlNext, True)
to:
'Sheet 2 name is assumed as "Sheet2". Change it to suit
Set r = Sheets("Sheet2").Range("A1:A300").Find(c.Value, [A1], xlValues, xlPart, _
xlByRows, xlNext, True)
You don't need to use wild card character then.

Edit: You can hard code the search range as shown above but that may not be good if you it is growing / dynamic.