• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

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

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
Massimo Scola
Asked:
Massimo Scola
  • 2
  • 2
2 Solutions
 
redmondbCommented:
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
 
telyni19Commented:
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
 
Massimo ScolaAuthor Commented:
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
 
Massimo ScolaAuthor Commented:
thanks
0
 
redmondbCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now