VBA Find Box

Hello,
can you please help,
I have a sheet with 5000 Rows, and 150 Columns.
Is there a macro (Find Box), to find in Column "A", and give me the Corresponding value.

Sample attached..

Example,
I need to find A0A IN Column "A" and Pack (Column "D"),
Result Should be 11.37$

I need to find A0R IN Column "A" and 6 (Column "J"),
Result Should be 15.31$

Any help is appreciated.
Thanks,
Sample.xlsx
W.E.BAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Why are those the correct answers?
Bill PrewIT / Software Engineering ConsultantCommented:
So you want to "press a button", be prompted for the Postal Code, and after you enter it have a pop up display the corresponding Pack value for it?

Or do you just want the spreadsheet to position to the Pack cell for that Postal Code?

~bp
Rory ArchibaldCommented:
I'd use INDEX/MATCH formulas - see attached.
Sample--1-.xlsx
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

W.E.BAuthor Commented:
Correct,  press a button,
a box to popups and give me the value.
thanks
Roy CoxGroup Finance ManagerCommented:
Can you remove the empty Column?
W.E.BAuthor Commented:
Column "B" has information,
I just didn't include in the sample.
W.E.BAuthor Commented:
Any help is appreciated.
ProfessorJimJamMicrosoft Excel ExpertCommented:
@Wass QA

download the attached file.

then press Control + Q to run the macro or run Macro called Lookuphelp

then it will ask you for the index range, select all the green cells in the attached workbook, then it will ask for match ID either type A0A or select single cell where A0A is placed, then it will ask for the match range select the orange range in column A and then it will ask for Column Match, either type the text Pack or select the cell where the text is, then it will ask for range of column, then select all the red range in the worksheet, then it will pop the result in the messagebox.
EE.xlsm
W.E.BAuthor Commented:
Hello,
thank you for your help,
it only recognizes the Envelope and Pack. (Column C & D)
when I enter numbers, i get the handling error.

thank you
aikimarkCommented:
What determines whether you get back the PACK column or the 6 (or some other column)?
ProfessorJimJamMicrosoft Excel ExpertCommented:
Wass QA,

it is fixed now.  please find attached revised. i have modified the code to accept both number or text. besides,
Numeric values on worksheet are stored as Doubles, not Singles, so i took care of that too in the code.

so, use the latest attached workbook and let me know.
EE.xlsm
W.E.BAuthor Commented:
Thank you Professor,
one last request,
I tried to set the ranges, but it again, will only work for Envelope and pack.

Set myrange = ActiveSheet.Range("C3:O4")
Set myrange3 = ActiveSheet.Range("A3:A4")
Set myrange5 = ActiveSheet.Range("C2:O2")

Your help is greatly appreciated.
W.E.BAuthor Commented:
Thank you Professor,
one last request,
I tried to set the ranges, but it again, will only work for Envelope and pack.

Set myrange = ActiveSheet.Range("C3:O4")
Set myrange3 = ActiveSheet.Range("A3:A4")
Set myrange5 = ActiveSheet.Range("C2:O2")

Your help is greatly appreciated.
aikimarkCommented:
You could also hide the columns you don't want to see, select the used cells, and then invoke the data form.  Your "criteria" would be the post code and the form would show just what you want to see.
ProfessorJimJamMicrosoft Excel ExpertCommented:
you are missing myrange2 and my range4

in order to get the result you need to give input to all ranges

lets say you want to retrieve value of $14.80 in G4

when the macro is run select  range C3:O4  for myrange
then for row input  either type A0R  OR select the cell where it has this value
then for row array select range  A3:A4  
then for column input either type 3  or select the cell it has this value
then for column array select range C2:O2
by then you will get the result of 14.80 in message box.
W.E.BAuthor Commented:
the ranges will always be the same. -- for example
Set myrange = ActiveSheet.Range("C3:O4")
Set myrange3 = ActiveSheet.Range("A3:A4")
Set myrange5 = ActiveSheet.Range("C2:O2")

the variables are
myrange2 = Application.InputBox("Type Postal Code", "Type Text", , , , , , Type:=1 + 2)
myrange4 = Application.InputBox("Type Weight", "Type text or number", , , , , , Type:=1 + 2)

so, all I need to enter will be (myrange2  and myrange4)

thanks
ProfessorJimJamMicrosoft Excel ExpertCommented:
in this case, then find the attached workbook.
EE.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
W.E.BAuthor Commented:
Superb, thank you,
and thanks to Aikmark.
W.E.BAuthor Commented:
Superb, Thank you very much.
and thanks to Aikmark.
ProfessorJimJamMicrosoft Excel ExpertCommented:
you are welcome Wass QA.  thanks for the feedback.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.