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?
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?
0
Bill PrewCommented:
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
0
Rory ArchibaldCommented:
I'd use INDEX/MATCH formulas - see attached.
Sample--1-.xlsx
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

W.E.BAuthor Commented:
Correct,  press a button,
a box to popups and give me the value.
thanks
0
Roy CoxGroup Finance ManagerCommented:
Can you remove the empty Column?
0
W.E.BAuthor Commented:
Column "B" has information,
I just didn't include in the sample.
0
W.E.BAuthor Commented:
Any help is appreciated.
0
ProfessorJimJamCommented:
@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
0
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
0
aikimarkCommented:
What determines whether you get back the PACK column or the 6 (or some other column)?
0
ProfessorJimJamCommented:
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
0
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.
0
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.
0
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.
0
ProfessorJimJamCommented:
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.
0
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
0
ProfessorJimJamCommented:
in this case, then find the attached workbook.
EE.xlsm
0

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.
0
W.E.BAuthor Commented:
Superb, Thank you very much.
and thanks to Aikmark.
0
ProfessorJimJamCommented:
you are welcome Wass QA.  thanks for the feedback.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.