PDA

View Full Version : Solved: Dependent in Invoicing



ndendrinos
08-04-2009, 01:10 PM
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
08-05-2009, 02:51 AM
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
08-05-2009, 06:22 AM
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
08-05-2009, 10:31 AM
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

Unload UserForm5
For Userform5

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



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
08-05-2009, 07:42 PM
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
08-05-2009, 09:09 PM
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
08-05-2009, 09:15 PM
Anytime...

ndendrinos
08-07-2009, 08:49 AM
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
08-08-2009, 03:46 PM
Try...

ndendrinos
08-08-2009, 07:09 PM
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
08-08-2009, 07:26 PM
Sorry ... wrong attachment ... Using the "et done" taht works better But still no UserForm5

ndendrinos
08-08-2009, 08:45 PM
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
08-08-2009, 08:55 PM
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
08-09-2009, 12:01 AM
OK!