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
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
