Solved: Dependent in Invoicing

[ndendrinos]

But without filter ... rather matching and selection of block of rows between empty rows like in sheet Customers(2) is this possible?
Example: from UserForm2 I choose "Toronto Toyota" I will get in UserForm5
Toyota Canada
Toronto Toyota
Autos Toyota Montreal

Many thanks
(I have visited Contextures)

[rbrhodes]

Hi nd,

I don't quite understand.

You want to chose Toronto Toyota and get all of Canada?

1) Toronto is in Canada
2) Montreal is in Canada
3) Toyota has factories in Canada
4) Toronto _thinks_ it IS Canada
5) Montreal is in Quebec
6) Quebec is in Canada (barely)
7) Toronto and Montreal have hockey teams
8) Hockey is Canadian
9) Some Canadians have driven, built or seen a Toyota
10) Some have played hockey

<etc>


So, with all of the above information the answer is obviously:

Isildas Kitchen!


Try this.

[ndendrinos]

Aha! a Westerner with a sense of the "humor" ... but not so fast mon ami.
You will see from my attachment that some "ship to" destinations do not have any common words with the "Bill to" addresses.... hence the business of choosing a block in "customer(2) that matches the "Bill to" address AND limited by an empty row at the top and bottom....unsurmontable he?
Solve this one and you win the Pulizer ... the Oscar ...the Election (you have done that one (barely) ... see where this is going?
Thanks again and seriously this one when you solve it should be inducted in the hall of fame (KB) and a copy sent to the "vooman" @ Contextures .

[mdmackillop]

As you put the data into "blocks", you can make use of that.
In your other subs, unload Userform5 rather than hiding it, otherwise you can get "wrong" results
[vba]
Unload UserForm5
[/vba]For Userform5
[vba]
Private Sub UserForm_Initialize()
Dim rng As Range, cel As Range
Dim val As String
'Chosen
val = UserForm2.ListBox1.Value
Set rng = Sheets("Customers (2)").Columns(1).Find(val).CurrentRegion.Columns(1)

For Each cel In rng.Cells
ListBox1.AddItem cel
Next
'Cleanup
Set rng = Nothing
End Sub

[/vba]

Use a Dynamic Range for Company so as to avoid the spaces in your Userform2 listbox

=OFFSET(Customers!$A$1,1,0,COUNTA(Customers!$A:$A)-1,1)

[rbrhodes]

Salut,

Westerner eh? Where you from I wonder. As going East to West is sort of relative to where you actually are...

Aha! a Westerner with a sense of the "humor" ... but not so fast mon ami.

MD, had already posted the solution by the time I got this so I simply incorporated his comments and code into this example.

- If you go to Insert/Names/Define you'll see the Dynamic Range definiton for 'Company'.

- Userform5 now unloads and reloads to refresh the list and provide correct results

- UserForm5 delivers the 'blocks from Customer (2) as per

[ndendrinos]

A big thank you to MD for his kind assistance ( a man of few words BUT always right ) ... to the Westerner I happen to reside in Collingwood.Ontario and thank you for revising the file for me, it works great.
Until next time.

P.S. Again the option to mark this message "solved" is not available to me ... can the moderator pls help? thank you.

[rbrhodes]

Anytime...

[ndendrinos]

Using rbrhodes last revision of file "dr-test-deux-et-one based on the kind contribution of MD I thought that keeping just sheet "Customers(2)" as a record of billing addresses as well as shipping addresses would be easy.
I deleted sheet "Customers" and have tried everything under the sun to get the thing going to no avail.
If the selection in Userform1 determines what "block" appears in Userform5
can it be done from one sheet only (Sheet "Customers(2)) and how?
Thank you.

[rbrhodes]

Try...

[ndendrinos]

Hello again dr and thanks for the revised "et done" copy it works great.
Working from a single sheet titled now "Shipto" instead of "Customer" I have been working FOR HOURS with the aim of showing just the "Bill to" addresses in UserForm1 like this sample.
Two problems here that I cannot address is that in named columnA (Company) I have blank rows) ... I posted on MrExcel for that solution ... Also somehow I only get to see few of the Bill to addresses ... AND when I choose a Billing Address the macro fails and UserForm5 does not show.
My attachment will make things clearer.
Hope you get to see this message and just in case again thank you for your help.
Nick

[ndendrinos]

Sorry ... wrong attachment ... Using the "et done" taht works better But still no UserForm5

[ndendrinos]

After deleting the source "Destination" from the Properties of UserForm5 I now get to see it But not the whole block.
Example when I click in B8 and choose Max's Kennel I get Userform5 alright BUT with just Max's Kennel in it (Les Chiens Motel) does not show.
Is there a solution to this?
I attach the last revision if unsolved then I will use the "et done" version
and stop driving you all crazy.

[ndendrinos]

Fuond it at last

Private Sub UserForm_Initialize()
    Dim rng As Range, cel As Range
    Dim val As String
     'Chosen
    val = UserForm2.ListBox1.Value
    Set rng = Sheets("Customers (2)").Columns(2).Find(val).CurrentRegion.Columns(2)
     
    For Each cel In rng.Cells
        ListBox1.AddItem cel
    Next
     'Cleanup
    Set cel = Nothing
    Set rng = Nothing
End Sub

Thank you DR HAVE A GOOD EVENING.

[rbrhodes]

OK!