Excel 2001 VBA Search & copy

[filmnoir]

Hi All,

I'm trying to put some VBA together to search for a name on five works sheet then copy the row by a defined number of columns to a specific row on another sheet.

Each sheet contains the name once and a list of skills in the cells I need to copy to create a master record.

I would also like it to match it exactly and if no match is found display a pop up saying no exact match found.

So far I have....

Sub skillsearch()

Dim strLastRow As String
Dim rngC As Range
Dim strToFind As String, FirstAddress As String
Dim wSht As Worksheet
Dim rngtest As String

Application.ScreenUpdating = False
Set wSht = Worksheets("Electricity")
strToFind = InputBox("Enter Consultants Name")
With wSht.Range("B7:B45")
Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
If Not rngC Is Nothing Then
FirstAddress = rngC.Address
Do
strLastRow = Sheets("Search Results").Range("A" & Rows.Count).End(xlUp).Row + 1
rngC.EntireRow.Copy Sheets("Search Results").Cells(strLastRow, 1)
Set rngC = .FindNext(rngC)
Loop While Not rngC Is Nothing And rngC.Address <> FirstAddress
End If
End With
MsgBox ("Finished")
 
End Sub

Any help appreciated....

 

[Jonmo1]

Welcome to the board...

To make it find Exact match..
Change
Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
to
Set rngC = .Find(what:=strToFind, LookAt:=xlWhole)


to make it display a message box when no match is found, change

End If
End With

to

Else
MsgBox "No match found"
End If
End With


Hope that helps.

 

[kpark91]

So what is wrong with the code you have?

and could you provide an example because I can't wrap my head around it without an example as I really lack concentration.

 

[filmnoir]

Thanks, I've added the code for the whole match.

The main issue is that I need for it to search all the sheets and each time it finds an exact match it needs to copy the row of data to a specific row on the target sheet.

At the moment it's copying it to row starting B2 for some reason...