Forum home » Delegate support and help forum » Microsoft Excel Training and help » Visual Basic - Automatically adding lines.
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Visual Basic - Automatically adding lines.
Resolved · Medium Priority · Version 2007
Reply Fri 17 Sep 2010, 13:40Delegate Chris said...
Chris has attended:
Excel Advanced course
Visual Basic - Automatically adding lines.
Hi There
I am trying to create a macro which draws (via a vlookup) from an ever expanding list of variables. I have found I could add to the following code each time I wanted to add products to the list, but as there are circa 10,000 products, I would prefer an automated means - is this possible?
Example Code:
Sheets("Sheet2").Select
Range("C7").Select
ActiveCell.FormulaR1C1 = "Product"
Range("D7").Select
ActiveCell.FormulaR1C1 = "Cycle"
Range("C8").Select
ActiveCell.FormulaR1C1 = "Sausage"
Range("D8").Select
ActiveCell.FormulaR1C1 = "D"
Range("C9").Select
ActiveCell.FormulaR1C1 = "Egg"
Range("D9").Select
ActiveCell.FormulaR1C1 = "D"
Range("C10").Select
ActiveCell.FormulaR1C1 = "Potato"
Range("D10").Select
ActiveCell.FormulaR1C1 = "A"
Range("C11").Select
ActiveCell.FormulaR1C1 = "Chicken"
Range("D11").Select
ActiveCell.FormulaR1C1 = "B"
Range("C12").Select
ActiveCell.FormulaR1C1 = "Lettuce"
Range("D12").Select
ActiveCell.FormulaR1C1 = "C"
Range("C13").Select
ActiveCell.FormulaR1C1 = "Chips"
Range("D13").Select
ActiveCell.FormulaR1C1 = "D"
Range("C14").Select
ActiveCell.FormulaR1C1 = "Burger"
Range("D14").Select
ActiveCell.FormulaR1C1 = "B"
Range("C15").Select
ActiveCell.FormulaR1C1 = "Sauce"
Range("D15").Select
ActiveCell.FormulaR1C1 = "C"
Range("F9").Select
Many thanks in advance
Chris
For upcoming training course dates see: Pricing & availability
RE: Visual Basic - Automatically adding lines.
Hi Chris, thanks for your query. You need to turn your list into a Dynamic Named Range and use that range name in your VLookup. Here's an explanation of dynamic named ranges.
http://www.ozgrid.com/Excel/DynamicRanges.htm
That should save you a lot of coding!
Hope this helps,
Anthony
Tue 28 Sep 2010: Automatically marked as resolved.
|