Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill down relating to cell value
Good afternoon all!
I have a problem which i'm sure some of you will find easy in Excel! I have a large two column sheet with a part number and a quantity. What i need to do is repeat/fill down the part number refering to the qty, ie: 123456 x 2 234567 x 3 would be 123456 123456 234567 234567 234567 Please help - the limitations of my labelling software are really getting to me! --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill down relating to cell value
Try this macro.
Sub FillDown() 'Pre cond: Part number in column A and qty in column B. ' Single data can't exist in cells of col A and B i.e. Data present in both An and Bn or nothing present in both 'Post cond: Results put in col D Dim i, j, k As Integer i = 0 j = 0 Do While Range("B1").Offset(i, 0) < vbNullString For k = 1 To Val(Range("B1").Offset(i, 0)) Range("D1").Offset(j, 0).Value = Range ("A1").Offset(i, 0).Value j = j + 1 Next k i = i + 1 Loop End Sub -----Original Message----- Good afternoon all! I have a problem which i'm sure some of you will find easy in Excel! I have a large two column sheet with a part number and a quantity. What i need to do is repeat/fill down the part number refering to the qty, ie: 123456 x 2 234567 x 3 would be 123456 123456 234567 234567 234567 Please help - the limitations of my labelling software are really getting to me! --- Message posted from http://www.ExcelForum.com/ . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill down relating to cell value
Thanks Gandei
I have tried to run the macro as you suggest, but get a 'compile error argument not optional' on 'value = range". Any ideas would be appreciated! Thanks in advance -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill down relating to cell value
Hi
Modify Gandei's code like this. (It's a problem with News Group) :D Code: -------------------- Range("D1").Offset(j, 0).Value = _ Range("A1").Offset(i, 0).Value -------------------- And what I suggest is as follows. Code: -------------------- Sub Test() 'Part number is placed in A col, Qty is placed in B col Dim c As Range, i As Long For Each c In Range([A1], [A65536].End(xlUp)) c.Offset(i, 2).Resize(c.Offset(, 1).Value).Value = c.Value i = i + c.Offset(, 1).Value - 1 Next End Sub -------------------- --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill down relating to cell value
Fantastic Colo!
Works perfectly! Have a nice evening -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill down relating to cell value
Fantastic Colo!
Works perfectly! Have a nice evening -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill down relating to cell value
Fantastic Colo!
Works perfectly! Have a nice evening -- Message posted from http://www.ExcelForum.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill down relating to cell value
Fantastic Colo!
Works perfectly! Have a nice evening -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove fill in text from a cell from an unpopulated fill-in cell | Excel Worksheet Functions | |||
Max (A1:A1), relating to other cells | Excel Worksheet Functions | |||
Relating cells | Excel Worksheet Functions | |||
Relating fields | Excel Discussion (Misc queries) | |||
total value relating to cell values in multiple columns! | Excel Discussion (Misc queries) |