Solved

How to determine a day/value based on three criterias?

Posted on 2013-02-06
5
189 Views
Last Modified: 2013-02-18
Hello Experts

I would like to look up a delivery day based on city , street name and street number.

some streets exist in several cities
I've created a userform which already does part of the job (see button on worksheet) and this is the second userform which I'd like to work:
How do I get the correct value?
This is part of the code which looks up the data. It returns the first day/postal code in the worksheet.

        PostalCode = Application.Evaluate("=LOOKUP(2,1/((StreetTable[Street]=""" & Me.cboStreet.Value & """)*(" & MakeNumeric(Me.txtNumber.Value) & ">=StreetTable[From])*(" & MakeNumeric(Me.txtNumber.Value) & "<=StreetTable[To])),StreetTable[PostalCode])")
        Delivery = Application.Evaluate("=LOOKUP(2,1/((StreetTable[Street]=""" & Me.cboStreet.Value & """)*(" & MakeNumeric(Me.txtNumber.Value) & ">=StreetTable[From])*(" & MakeNumeric(Me.txtNumber.Value) & "<=StreetTable[To])),StreetTable[Delivery])")
        City = Application.Evaluate("=LOOKUP(2,1/((StreetTable[Street]=""" & Me.cboStreet.Value & """)*(" & MakeNumeric(Me.txtNumber.Value) & ">=StreetTable[From])*(" & MakeNumeric(Me.txtNumber.Value) & "<=StreetTable[To])),StreetTable[City])")

Open in new window


The lookup is difficult as the same street exists in several cities,

What do I need to do in order to get the correct days/postal code?

Thanks
Massimo
Search.xlsm
0
Comment
Question by:Massimo Scola
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 26

Assisted Solution

by:redmondb
redmondb earned 250 total points
ID: 38864310
Hi, Massimo.

The cleanest way to do this is to add a "Lookup" column which concatenates the City and Street (e.g. Luzern:Adligenswilerstrasse). Is this acceptable to you? If so, can a city+street combination have multiple entries, e.g. one for houses 1 to 100 and a second for houses 200 to 300?

Thanks,
Brian.
0
 
LVL 12

Accepted Solution

by:
telyni19 earned 250 total points
ID: 38865666
I suggest using VLookup formulas, and, like the previous commenter, adding a concatenated column.

See attached for my suggested solution which has the second form fully functioning. Note that I first set up a completely worksheet-based mock-up in columns L and M to construct the initial formulas, as a display of how the solution works, and then I translated them into your code. The worksheet-based formulas use named ranges in order to make the formulas more understandable. It's currently populated with the "difficult" combination you highlighted, showing that it gets the correct result.

Once you're satisfied with the result, you can delete columns L and M and hide column A to make the sheet look exactly as it did before. Hiding column A won't interfere with the vlookups.

As a side note, I don't think you need the MakeNumeric function; the built-in Val function works for the purposes of the form.

The vlookup worksheet function is used multiple times with similar constructions, so I encapsulated it in a function whose main statement says this (with variables for the inputs):

    VLookEval = WorksheetFunction.VLookup(strStreet & strCity, Range("StreetTable[[#All],[Lookup]:[Delivery]]"), intCol, False)

The first input to VLookup is the lookup value, which is the concatenated street and city. The second input is the data table to search. The third input is the column number to return when the value is found. The fourth input is a boolean asking whether to check approximate values; for exact lookups, it's always False.

Since I added the column at the left side of the table (required positioning to make the vlookups work), I also corrected the form initialization by having it use the same variable column determination as the Lookup button's code in order to allow for any other additions to the table.

The code is fully commented, so take a look and see what you think.
Search-Vlookup.xlsm
0
 

Author Comment

by:Massimo Scola
ID: 38901158
Hello

I tried Brian's attempt first and it took me a long time and it didn't work - even though I had the same idea in mind. Then I tried telny's  workbook. It works with the sample data but when I tried it with the actual data and streets it didn't work because ....

The streets were retrieved from another workbook and they contained whitespace. I used following function in the range "streets":

   Dim cCell As Range
   For Each cCell In Selection
      On Error Resume Next
      cCell.Value = RTrim(cCell.Value)
   Next cCell

Open in new window

 
to remove the whitespace and everything works fine now!

Thanks Brian and telyni19
0
 

Author Closing Comment

by:Massimo Scola
ID: 38901161
thanks
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38901497
Thanks, mscola.

I tried Brian's attempt first and it took me a long time and it didn't work
If it's not working for you then most experts would prefer you to come back sooner rather than later - they're usually well-experienced at getting to the heart of problems.

Regards,
Brian.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question