VBA - Multiple Values for one cell input

[Seandobson2402]

Good Morning,

I am trying to find a way to populate data into columns, based on a postcode entered into Shee7 (Postcode) "I14" and assigning a macro to a button titled search - which runs the function / routine once pressed. If no postcode is entered - I want a MsgBox "Please enter postcode" and if the postcode is not found, I would like a MsgBox saying "Postcode Not Found".

My main problem is returning multiple matches. I have 99000 rows of data on my main information sheet (Sheet1 "PC Data") Each post code can be used multiple times and have lots of address' linking to the postcode.

Below is the postcode sheet I want the sub / function to show information - B18 - N18 are the headers and I would like the values to be put below them (B-N19 and onwards)

Sheet7 (Postcode)
B18 (B2F ID)
C18 (UPRN)
D18 (Premise ID)
E18 (Post Code)
F18 (Address Line 1)
G18 (Address Line 2)
H18 (Address Line 3)
I18 (Customer Account Status)
J18 (SO CAT)
K18 (SME/MLE CAT)
L18 (Civils Costs)
M18 (Date Complete)
N18 (Comments)

Shee1 (PC Data) - source sheet (row 1 = headers and row 2 downwards = data to match)
A2 (B2F ID)
H2 (UPRN)
I2 (Premise ID) - Lookup Column
R2 (Post Code)
N2 (Address Line 1)
O2 (Address Line 2)
P2 (Address Line 3)
Y2 (Customer Account Status)
BL2 (SO CAT)
BM2 (SME/MLE CAT)
AN2 (Civils Costs)
E2 (Date Completed)
BX2 (Comments)


Basically, once the postcode has been entered and I press search. I want the routine to look through Sheet1, find the post code, copy the information detailed on that row into Sheet7, loop through the data and look for the next row that matches the postcode and do the same until no more are found.

Thanks for you help guy. Hopefully this can be done!​

​

 

[Seandobson2402]

Perfect man. Thank you very much – this is amazing. VBA is getting more and more exciting each day!

One last thing – is there a way to paste special the copied cells instead of just pasting? I tried adding .PasteSpecial xlPasteValues . Needless to say, this didn’t work.

 

[Fluff]

Change the copy segment like this

         .Range("B" & NxtRw).Value = Fnd.Offset(, -17).Value
         .Range("C" & NxtRw).Resize(, 2).Value = Fnd.Offset(, -10).Resize(, 2).Value
         .Range("E" & NxtRw).Value = Fnd.Offset.Value