Hi
I'm looking to increase the functionality in an invoicing program I'm working on.
I would like to have auto recognition of delivery address using VBA in excel.
VBA seems to be a necessity as the cell that displays the delivery address needs to be autopopulated and then allow modification.
CELLS
E11 = Folio number for a customer.
E15 = Delivery method.
Cells B19:B31 - these cells contain order numbers and represent a line on the invoice.
B39 = note section.
SHEETS
'Invoice' = this sheet is used for entry of information.
'Del' = this sheet is used to story delivery address information.
The 'Del' sheet looks like this:
<tbody>
</tbody>Hi
I'm looking to increase the functionality in an invoicing program I'm working on.
I would like to have auto recognition of delivery address using VBA in excel.
VBA seems to be a necessity as the cell that displays the delivery address needs to be autopopulated and then allow modification.
CELLS
E11 = Folio number for a customer.
E15 = Delivery method.
Cells B19:B31 - these cells contain order numbers and represent a line on the invoice.
B39 = note section.
SHEETS
'Invoice' = this sheet is used for entry of information.
'Del' = this sheet is used to story delivery address information.
The 'Del' sheet looks like this:
<tbody>
</tbody>
The checking has to happen in the following order:
1. Folio number is selected
2. Delivery method is selected.
There are two options when the 'Del' sheet needs to be used to lookup an address:
UPS
Linkline
If any another option is selected here (like 'Collected') then we don't need to run the code.
3. Once we have to lookup an address we need the following to happen:
a) Match the folio on the 'Del' sheet to look up possible addresses.
b) See if the part of the order number (in cells B19 up to B31) that decides the address is a suffix or a prefix.
c) If there are multiple order numbers in B19:B31 and the resulting delivery addresses are different – then we need to show a popup stating ‘CONFLICTING DELIVERY ADDRESSES – Please verify’. With just an option to bypass the popup.
Typically the first order number has the same prefix/suffix as the ones below it.
d) Then match the address from column D and dump this into cell B39 in the following format:
Sent via [delivery_method] to [delivery_address]
delivery_method = 'Invoice'=E15
delivery_address = 'Del'=D
I am sure this is doable - just not clear on how I am to implement this - especially considering that B39 should be populated as and when I enter in the order number for each line on the invoice...so a bit like a formula.
I don't know how to select between two formulae (that look for a prefix or a suffix based on 'Del' column B.). I was going to use a formula to perform the lookup and then have a button that copies the lookup into that cell so that I can edit it if I choose... But that looks like a silly way of doing it.
Thanks.
I'm looking to increase the functionality in an invoicing program I'm working on.
I would like to have auto recognition of delivery address using VBA in excel.
VBA seems to be a necessity as the cell that displays the delivery address needs to be autopopulated and then allow modification.
CELLS
E11 = Folio number for a customer.
E15 = Delivery method.
Cells B19:B31 - these cells contain order numbers and represent a line on the invoice.
B39 = note section.
SHEETS
'Invoice' = this sheet is used for entry of information.
'Del' = this sheet is used to story delivery address information.
The 'Del' sheet looks like this:
| A | B | C | D | |
| 1 | FOLIO | LOCATION | MATCH | ADDRESS |
| 2 | W14 | SUFFIX | 9 | 1 Letsby Avenue, Coventry, CV1 1AA |
| 3 | W14 | SUFFIX | 6 | 4 Some Place, Leicester, LE1 4AA |
| 4 | W14 | SUFFIX | 4 | 59 Another Toon, Leeds, L2 4AA |
| 5 | T02 | PREFIX | 52 | 52 Worthing Add, Truro, TR1 1AA |
| 6 | T02 | PREFIX | 47 | 494 Units, Biz, B2 3AA |
| ... |
<tbody>
</tbody>
I'm looking to increase the functionality in an invoicing program I'm working on.
I would like to have auto recognition of delivery address using VBA in excel.
VBA seems to be a necessity as the cell that displays the delivery address needs to be autopopulated and then allow modification.
CELLS
E11 = Folio number for a customer.
E15 = Delivery method.
Cells B19:B31 - these cells contain order numbers and represent a line on the invoice.
B39 = note section.
SHEETS
'Invoice' = this sheet is used for entry of information.
'Del' = this sheet is used to story delivery address information.
The 'Del' sheet looks like this:
| A | B | C | D | |
| 1 | FOLIO | LOCATION | MATCH | ADDRESS |
| 2 | W14 | SUFFIX | 9 | 1 Letsby Avenue, Coventry, CV1 1AA |
| 3 | W14 | SUFFIX | 6 | 4 Some Place, Leicester, LE1 4AA |
| 4 | W14 | SUFFIX | 4 | 59 Another Toon, Leeds, L2 4AA |
| 5 | T02 | PREFIX | 52 | 52 Worthing Add, Truro, TR1 1AA |
| 6 | T02 | PREFIX | 47 | 494 Units, Biz, B2 3AA |
| ... |
<tbody>
</tbody>
The checking has to happen in the following order:
1. Folio number is selected
2. Delivery method is selected.
There are two options when the 'Del' sheet needs to be used to lookup an address:
UPS
Linkline
If any another option is selected here (like 'Collected') then we don't need to run the code.
3. Once we have to lookup an address we need the following to happen:
a) Match the folio on the 'Del' sheet to look up possible addresses.
b) See if the part of the order number (in cells B19 up to B31) that decides the address is a suffix or a prefix.
c) If there are multiple order numbers in B19:B31 and the resulting delivery addresses are different – then we need to show a popup stating ‘CONFLICTING DELIVERY ADDRESSES – Please verify’. With just an option to bypass the popup.
Typically the first order number has the same prefix/suffix as the ones below it.
d) Then match the address from column D and dump this into cell B39 in the following format:
Sent via [delivery_method] to [delivery_address]
delivery_method = 'Invoice'=E15
delivery_address = 'Del'=D
I am sure this is doable - just not clear on how I am to implement this - especially considering that B39 should be populated as and when I enter in the order number for each line on the invoice...so a bit like a formula.
I don't know how to select between two formulae (that look for a prefix or a suffix based on 'Del' column B.). I was going to use a formula to perform the lookup and then have a button that copies the lookup into that cell so that I can edit it if I choose... But that looks like a silly way of doing it.
Thanks.
