Using a macro to find..

[jharcourt1]

Hi all,

Need a bit of help with a macro to produce find results:

Basically i need whatever is entered into cell B8 to be found elsewhere in the spreadsheet and have the cell beneath that copied.

Its a basic address search function, you type the name of the business into cell B8, click the created 'Button A' it will locate the company name elsewhere on the spreadsheet and copy the cell beneath it (postcode).

The reason the copy is required is for a search on the internet to verify, so basically all you have to do in order to obtain postcode is type the company name and then the button.

Any help?

Cheers,
James

 

[VoG]

Try this: change C8 to suit

Sub cpy()
Dim Found As Range
Set Found = Cells.Find(what:=Range("B8").Value, after:=Range("B8"), LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing Then Found.Offset(1).Copy Destination:=Range("C8")
End Sub

 

[jharcourt1]

sorry forgot to mention i am a begginger. i can't use VBA.. if thats what the above is a code in?

 

[VoG]

Press ALT + F11 to open the Visual Basic Editor. Select Module from the Insert menu, copy the code and paste it into the white space on the right. change C8 to the cell that you want the result in. Press ALT + Q to close the code window.

Press ALT + F8, click on cpy then click the Run button.

 

[jharcourt1]

Sorry, i dont want the result anywhere.. i literally would like the macro to find what is in B8 somewhere else in the spreadsheet and copy the cell beneath it.

 

[VoG]

Then try

Sub cpy()
Dim Found As Range
Set Found = Cells.Find(what:=Range("B8").Value, after:=Range("B8"), LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing Then Found.Offset(1).Copy
End Sub

 

[jharcourt1]

no, doesnt seem to do anything?

any other suggestions?

 

[VoG]

It works for me. Maybe

Sub cpy()
Dim Found As Range
Set Found = Cells.Find(what:=Range("B8").Value, after:=Range("B8"), LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing Then
    Found.Offset(1).Copy
Else
    MsgBox "Not found"
End If
End Sub

 

[jharcourt1]

works perfectly! thank you