Find based on cell contents??

[M4RSHMAN85]

Hi All,

Currently working through creating some invoice automation and I've run in to a spot with locate the customers address based on a variable name. Here is what I've come up with so far. Any help would be great - Thank you.

Sub Macro5()
'
' Macro5 Macro
'

'Get Name'
Windows("BOOKS.xlsm").Activate
Range("B3").Select
Selection.End(xlDown).Select
Selection.Copy

'Paste Name'
Windows("Invoice Template.xlsx").Activate
Range("B7:C7").Select
ActiveSheet.Paste
Application.CutCopyMode = False

'Copy Name'
Selection.Copy

'Go To addresses sheet'
Sheets("Invoice Address Book").Select

*****'Find Selection Contents'*****

*****'RANGE IS B3:BA100'*****

Cells.Find(What:="******", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

'Copy Address'
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy

'Paste Address'
Sheets("Invoice").Select
ActiveSheet.Paste

'Select Name & End'
Range("B7:C7").Select

End Sub

 

[Skyybot]

When you want to find "Selections Contents" what are the "Contents"? What column(s) contain the data that will match the "Contents"?
Can you post a copy of your Workbooks (please make sure any sensitive information is removed)?

 

[M4RSHMAN85]

When you want to find "Selections Contents" what are the "Contents"? What column(s) contain the data that will match the "Contents"?
Can you post a copy of your Workbooks (please make sure any sensitive information is removed)?

Hi, The contents are the customer name copied from "my books". I have a sheet in my invoice template labelled Invoice Address Book. All my customers in here.
The Range to find the customer name (with the address in the 4-5 rows below it) is RANGE IS B3:BA100. Cols B & C are A, D & E are B etc.

Hope this makes sense?

 

[M4RSHMAN85]

Using alternative solution

 

[Fluff]

Good to hear you got the solution.
If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[M4RSHMAN85]

For me, I've decided to change my layout and now run a vlookup to return what I need - this is my solution.

The original problem is still unresolved.