Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 595
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro recorded... tabs & file names changed, macro hangs Steve Excel Worksheet Functions 3 October 30th 09 12:41 PM
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort Gavin Excel Worksheet Functions 0 May 17th 07 01:20 PM
how to count/sum by function/macro to get the number of record to do copy/paste in macro tango Excel Programming 1 October 15th 04 01:16 PM
macro to delete entire rows when column A is blank ...a quick macro vikram Excel Programming 4 May 3rd 04 08:45 PM
Start Macro / Stop Macro / Restart Macro Pete[_13_] Excel Programming 2 November 21st 03 06:04 PM


All times are GMT +1. The time now is 02:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"