Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do I need a Macro?
Hello, I have a worksheet that contains distance intervals (From, To) however there are gaps between the intervals, (below) ID From (ft) To (ft) 1 1 4 2 6 8 3 8 12 4 17 20 5 20 25 6 25 30 7 36 40 I would like to fill in the gaps to display a continuous set of intervals (below). ID From (ft) To (ft) 1 1 4 **1.5 4 6* 2 6 8 3 8 12 *3.5 12 17 4 17 20 5 20 25 6 25 30 **6.5 30 36* 7 36 40 I think I need a macro to perform this task but any insight would be helpful. The macro needs to be able to recognize a gap between a “To” value and the following “From” value. It then needs to insert a row between the interval gap and fill in the fields with the appropriate values. Thank you, thank you, thank you. Tim K -- tking ------------------------------------------------------------------------ tking's Profile: http://www.excelforum.com/member.php...o&userid=24811 View this thread: http://www.excelforum.com/showthread...hreadid=383679 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do I need a Macro?
Tim:
This seems to work Sub FillGaps() Dim lIdx As Long Dim rRng As Range With Sheet1 Set rRng = .Range("A2", .Range("A" & ..Rows.Count).End(xlUp).Offset(-1, 0)) End With For lIdx = rRng.Cells.Count To 1 Step -1 With rRng.Cells(lIdx) If .Offset(0, 2).Value < .Offset(1, 1).Value Then .Offset(1, 0).EntireRow.Insert .Offset(1, 0).Value = .Value + 0.5 .Offset(1, 1).Value = .Offset(0, 2).Value .Offset(1, 2).Value = .Offset(2, 1).Value End If End With Next lIdx End Sub -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com tking wrote: Hello, I have a worksheet that contains distance intervals (From, To) however there are gaps between the intervals, (below) ID From (ft) To (ft) 1 1 4 2 6 8 3 8 12 4 17 20 5 20 25 6 25 30 7 36 40 I would like to fill in the gaps to display a continuous set of intervals (below). ID From (ft) To (ft) 1 1 4 **1.5 4 6* 2 6 8 3 8 12 *3.5 12 17 4 17 20 5 20 25 6 25 30 **6.5 30 36* 7 36 40 I think I need a macro to perform this task but any insight would be helpful. The macro needs to be able to recognize a gap between a “To” value and the following “From” value. It then needs to insert a row between the interval gap and fill in the fields with the appropriate values. Thank you, thank you, thank you. Tim K |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |