Home 
Search 
Today's Posts 
#1




Inserting Rows based on cell differences and interpolating in between
Hello 
I have a complicated issue. I have a file with 5 columns (A, B, C, D, E). A is fixed string value. B is a number increasing irregularly. C, D, E, are given values. What I need to do is have column B regular (i.e. every 1). So I have to insert columns between B (i.e. B2B1) will determine how many rows between B1 & B2 and then B3B2 will determine how many rows between B2 & B3 and so on. After this inserting the rows, I have to linear interpolate columns C, D & E in the newly create empty columns. In all cases I want to keep the original values and interpolate between them. The other issue is that column B has decimal/fraction, but I think I can round this to the nearest integer to make it easier for interpolation. 
#2




Inserting Rows based on cell differences and interpolating in between

#3




Inserting Rows based on cell differences and interpolating in between
I tried this one but I think I have a problem with the insert loop!
Option Explicit Sub Test01() Application.ScreenUpdating = False Dim numRows As Long Dim r As Long Dim Rng As Range Dim lastrw As Long Dim Ar As Range Dim StepValue1 Dim StepValue2 Dim StepValue3 Dim Ar1 As Range Dim AR2 As Range Dim i As Integer lastrw = Cells(Rows.Count, "A").End(xlUp).Row i = 1 For i = i + 0 To lastrw Step 1 Set Rng = Range(Cells(i, "A"), Cells(lastrw, "A")) numRows = Cells(i + 1, 2).Value  Cells(i + 0, 2).Value For r = Rng.Rows.Count To 1 Step 1 Rng.Rows(r + i).Resize(numRows  1).EntireRow.Insert Next r Next i Set Rng = Columns(1).SpecialCells(xlBlanks) For Each Ar In Rng.Areas Set Ar1 = Ar.Offset(1, 0).Resize(Ar.Rows.Count + 1) Set AR2 = Ar1.Resize(Ar1.Rows.Count + 1) StepValue1 = (AR2(AR2.Count).Offset(0, 2)  _ Ar1(1).Offset(0, 2)) / Ar1.Count StepValue2 = (AR2(AR2.Count).Offset(0, 3)  _ Ar1(1).Offset(0, 3)) / Ar1.Count StepValue3 = (AR2(AR2.Count).Offset(0, 4)  _ Ar1(1).Offset(0, 4)) / Ar1.Count Ar1.Offset(0, 2).DataSeries Rowcol:=xlColumns, _ Type:=xlLinear, Date:=xlDay, _ Step:=StepValue1, Trend:=False Ar1.Offset(0, 3).DataSeries Rowcol:=xlColumns, _ Type:=xlLinear, Date:=xlDay, _ Step:=StepValue2, Trend:=False Ar1.Offset(0, 4).DataSeries Rowcol:=xlColumns, _ Type:=xlLinear, Date:=xlDay, _ Step:=StepValue3, Trend:=False Next End Sub 
#4




Inserting Rows based on cell differences and interpolating in between
Hello 
I have a complicated issue. I have a file with 5 columns (A, B, C, D, E). A is fixed string value. B is a number increasing irregularly. C, D, E, are given values. What I need to do is have column B regular (i.e. every 1). So I have to insert columns between B (i.e. B2B1) will determine how many rows between B1 & B2 and then B3B2 will determine how many rows between B2 & B3 and so on. After this inserting the rows, I have to linear interpolate columns C, D & E in the newly create empty columns. In all cases I want to keep the original values and interpolate between them. The other issue is that column B has decimal/fraction, but I think I can round this to the nearest integer to make it easier for interpolation. My approach would be to separate adding more rows from the business logic. I use something like this... Sub InsertBlankRows(Optional Position As String) ' Inserts a specified number of rows at the location specified. ' If the Position arg is not used then the default is ActiveCell.Row. Dim vRows As Variant, lPos As Long Const sMsg As String = "Enter the number of rows to insert." 'Evaluate user input On Error Resume Next vRows = InputBox(Prompt:=sMsg, Default:=1): If vRows = "" Then Exit Sub '//user cancels If Not Err = 0 Or Not IsNumeric(vRows) Or Not vRows = 1 Then Exit Sub 'Get the position to insert lPos = IIf(Position = "Below", lPos + 1, ActiveCell.Row) 'Insert the rows ActiveSheet.Rows(lPos).Resize(vRows).Insert Shift:=xlDown End Sub 'InsertBlankRows ...and use it like this... Sub AddMoreRows() Dim vAns, sPos$ vAns = MsgBox("Insert rows ABOVE here?", vbYesNo, "Insert Rows") sPos = IIf(vAns = vbYes, "Above", "Below") InsertBlankRows sPos End Sub  Garry Free usenet access at http://www.eternalseptember.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion 
#5




Inserting Rows based on cell differences and interpolating in between
Could you please explain a little bit this code? Thanks.

#6




Inserting Rows based on cell differences and interpolating in between

#7




Inserting Rows based on cell differences and interpolating in between
Other posts I have in other forums  I apologize for not posting these earlier:
http://www.vbaexpress.com/forum/show...tween&p=348053 http://www.excelbanter.com/showthread.php?t=451909 http://www.ozgrid.com/forum/showthread.php?t=200863 http://www.mrexcel.com/forum/excelq...ml#post4612064 
#8




Inserting Rows based on cell differences and interpolating in between
Other posts I have in other forums  I apologize for not posting these earlier:
http://www.mrexcel.com/forum/excelq...gbetween.html http://www.vbaexpress.com/forum/show...tween&p=348053 http://www.excelforum.com/showthread...2555&p=4461876 http://www.ozgrid.com/forum/showthre...00863&p=776001 
#9




Inserting Rows based on cell differences and interpolating in between
Corretion...
Sub InsertBlankRows(Optional Position As String) ' Inserts a specified number of rows at the location specified. ' If the Position arg is not used then the default is ActiveCell.Row. Dim vRows As Variant, lPos As Long Const sMsg As String = "Enter the number of rows to insert." 'Evaluate user input On Error Resume Next vRows = InputBox(Prompt:=sMsg, Default:=1): If vRows = "" Then Exit Sub '//user cancels If Not Err = 0 Or Not IsNumeric(vRows) Or Not vRows = 1 Then Exit Sub 'Get the position to insert lPos = ActiveCell.Row: If Position = "Below" Then lPos = lPos + 1 'Insert the rows ActiveSheet.Rows(lPos).Resize(vRows).Insert Shift:=xlDown End Sub  Garry Free usenet access at http://www.eternalseptember.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion 
#10




Inserting Rows based on cell differences and interpolating in between
Could you please explain a little bit this code? Thanks.
' Inserts a specified number of rows at the location specified. ' If the Position arg is not used then the default is ActiveCell.Row.  Garry Free usenet access at http://www.eternalseptember.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion 
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Inserting and copying Rows based on a variable  Excel Programming  
need help please inserting multiple rows based on cell value then copying to data sheet  Excel Worksheet Functions  
Inserting rows based on count  Excel Programming  
Inserting rows based on another cells value  Excel Programming  
Inserting rows based on another cells value  Excel Programming 