Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Fill series but a little different
Hi, ı want to fill series based on cell data, a sample is below a1 b1 james 10 jane 8 ... .. result should be james 1 james 2 james 3 ... james 10 jane 1 jane 2 ... jane 8 thanks for any help. -- kingjeremy ------------------------------------------------------------------------ kingjeremy's Profile: http://www.excelforum.com/member.php...o&userid=26444 View this thread: http://www.excelforum.com/showthread...hreadid=397155 |
#2
|
|||
|
|||
kingjeremy Wrote: Hi, ı want to fill series based on cell data, a sample is below a1 b1 james 10 jane 8 ... .. result should be james 1 james 2 james 3 ... james 10 jane 1 jane 2 ... jane 8 thanks for any help. Hi kingjeremy Not 100% sure what you want but here goes If James is in cell A1 and 1 is in B1, select both cells, move cursor to bottom right of selected cells, it becomes a black cross, hold down left mouse button and drag down or if James is in Cell A1 and 10 is in B1 to fill the gaps back to James 1, you will need to insert rows above James 10 and then do the same thing but drag upwards -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=397155 |
#3
|
|||
|
|||
Are you working with Mellissa???
Try this against a copy of your workbook--it'll destroy the original data. Option Explicit Sub testme() Dim wks As Worksheet Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long Dim howMany As Long Set wks = Worksheets("sheet1") With wks FirstRow = 2 'headers in row 1??? LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 howMany = .Cells(iRow, "B").Value If howMany 1 Then .Rows(iRow + 1).Resize(howMany - 1).Insert .Cells(iRow, "A").Resize(howMany).Value _ = .Cells(iRow, "A").Value With .Cells(iRow, "B").Resize(howMany) .Formula = "=row()-" & iRow - 1 .Value = .Value End With End If Next iRow End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm And there's no validation against the value in column B. It can blow up really good if you don't type in nice numbers. kingjeremy wrote: Hi, ı want to fill series based on cell data, a sample is below a1 b1 james 10 jane 8 .. .. result should be james 1 james 2 james 3 .. james 10 jane 1 jane 2 .. jane 8 thanks for any help. -- kingjeremy ------------------------------------------------------------------------ kingjeremy's Profile: http://www.excelforum.com/member.php...o&userid=26444 View this thread: http://www.excelforum.com/showthread...hreadid=397155 -- Dave Peterson |
#4
|
|||
|
|||
er, Melissa???
|
#5
|
|||
|
|||
no I'm neither working with her nor I'm melissa. It was just a coincidence that we asked the same question almost at the same time. And thanks for the solution it will really ease a lot of work. -- kingjeremy ------------------------------------------------------------------------ kingjeremy's Profile: http://www.excelforum.com/member.php...o&userid=26444 View this thread: http://www.excelforum.com/showthread...hreadid=397155 |
#6
|
|||
|
|||
Glad it worked for you (and Melissa, too <bg).
kingjeremy wrote: no I'm neither working with her nor I'm melissa. It was just a coincidence that we asked the same question almost at the same time. And thanks for the solution it will really ease a lot of work. -- kingjeremy ------------------------------------------------------------------------ kingjeremy's Profile: http://www.excelforum.com/member.php...o&userid=26444 View this thread: http://www.excelforum.com/showthread...hreadid=397155 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel should not automatically extend series or fill values | Excel Discussion (Misc queries) | |||
Fill Series option grayed out | Excel Discussion (Misc queries) | |||
fill series | Excel Discussion (Misc queries) | |||
I've created a custom fill series. Now I don't know how to use i. | Excel Discussion (Misc queries) | |||
fill series grayed out (not available, disactivated) | Excel Discussion (Misc queries) |