My question, what is the Vba code
(Excel version 2007)
I want vba code for customer address in range (E11:E18)
At this moment he stopt at the last not empty cell in kolumn "E" , cell "E47"
See my file
Thanks for help
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Vba code Excel 2007 version
0
Discussion
Thank you for the reply
But the address is not always fully like this
Sometimes example 1
Company
Street 55 /B4
0091 City
Example 2
Company
Purchase Department
Street 78
0080 City
Example 3
Mr...
Street 54
0030 city
Vat number
Then you have blanks cells in the address list
The address must be always one list, no blanks cells in the address
Thanks you for the help
But the address is not always fully like this
Sometimes example 1
Company
Street 55 /B4
0091 City
Example 2
Company
Purchase Department
Street 78
0080 City
Example 3
Mr...
Street 54
0030 city
Vat number
Then you have blanks cells in the address list
The address must be always one list, no blanks cells in the address
Thanks you for the help
Seppe (rep: 4) Mar 30, '20 at 2:51 am
The solution of this problem can't be in the method of transferring the data, whether by worksheet function or VBA. It must be in the design of the database and the columns available there, as well as the design of the invoice template and the matching fields available in it. Note that you can combine several columns into one line and skip DB cells that are blank. For example, the ZIP code is generally placed into its own column in such DBs. The less effort you put into the DB design the more trouble you will have in its use, the less useful it will be beyond this one purpose. Generally speaking, a list of customers with info about them is one of the more important assets of any business. Extracting data from it for an invoice is just one of its many uses. Make sure you don't get the cart before the horse.
Variatus (rep: 4889) Mar 30, '20 at 5:07 am
Answers
0
Selected Answer
For this task you don't need VBA. But you do need a proper database design for your Sheet2. I have arranged the sheet for you. Please look at the attached workbook.
You will see that all data are in the range A2:I4. The formula below makes this address dynamic, meaning, as you add more customer numbers the range grows automatically. I used this formula to define the range I named "Customers".
=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,COUNTA(Sheet2!$1:$1))Explainer: Start at Sheet2!A2, move 0 rows down and 0 columns to the right and then extend the selection down by as many entries as there are in column A not counting 1 caption column, and as many columns to the right as there are entries in row 1. So, don't leave any blanks in row 1 or column A.
Now you can access your data with a simple VLookup. To save you the time of typing the customer number in H9 of your Sheet1, and to avoid any possibility of typos in that process, select the customer form a drop-down list. In the attached workbook I created such a drop-down for you. Observe the formula for setting the list.
=INDEX(Customers,,1)The list is created from the same named range as I set up for the data, just using the first column of it. Therefore this list inherits its dynamism from the Customers named range.
Now that all preparations are done you can construct the VLookup formula in Sheet1!E11. Here it is.
=VLOOKUP($H$9,Customers,ROW()-9, FALSE)Explainer: Look for the value in H9 in the first column of the range "Customers". When found, go as many columns to the right as of this row's number (that is 11 in E11), minus 9. 11-9 = 2, and return the value found there. As you copy this formula down, ROW() increases. So the formula will work out to 12-9, 13-9, 14-9 etc, enumerating the columns in your customer table.
Talking of tables, you should consider using a table for your DB on Sheet2. I didn't want to add one more straw to this load, but it'll be easier to manage going forward if you make such a change.