Need a Macro plz !
I have about 5000 rows of data
I must insert nine blank rows between each two rows
this is the easy part till now...
then i want to fill these nine blank rows with series (linear-trend)
the problem that I want to fill each nine blank rows
seprately...because when i fill all the blank rows... they change the
values in the original rows !!!
im trying to interpolate values (the nine blank rows) between the
two original rows ...mainly i have X & Y values along with other
values but i need interpolation only for the X & Y .
this is the code i used to insert the blank 9 rows...i wish any one can complete it concerning teh filling values issue
Option Explicit
Sub InsertBlankRows()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim r As Long
Dim Rng As Range
Dim lastrw As Long
numRows = InputBox("How many Rows")
lastrw = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range(Cells(1, "A"), Cells(lastrw, "A"))
For r = Rng.Rows.Count To 1 Step -1
Rng.Rows(r + 1).Resize(numRows).EntireRow.Insert
Next r
Application.ScreenUpdating = True
End Sub
you can look at this screenprint ... i think it explian
http://img436.imageshack.us/img436/1491/excel1vq.jpg
http://img436.imageshack.us/my.php?image=excel1vq.jpg
# 1 Re: Need a Macro plz !
What are you trying to DO with those values?
Laurel at 2007-11-11 17:27:30 >
# 2 Re: Need a Macro plz !
i have many X & Y values ... between each two values i want to insert NINE VALUES ( i.e. interpolate nine values between each two original values)
check the screen shot
# 3 Re: Need a Macro plz !
Ok, you might be a geophysicist, but ya dun't hafta use "BIG" words like interpolate!
Can't ya just use words we'll understand, like "insert"? :-
I don't program in Excel so I'll hafta think about this on my way home. While I'm driving home, look at this and see if it helps:
http://support.microsoft.com/?kbid=213318
You can also google it! Try "inserting blank rows in macros"
L
Laurel at 2007-11-11 17:29:28 >
# 4 Re: Need a Macro plz !
Laurel,
"interpolate" does not mean "insert"!
Say you have two numbers A and B, and you want a series of 10 numbers between the two of them. To compute the new numbers using a linear interpolation, you can use:
X(i) = A + i * (B - A) / 10
so that X(0) = A and X(10) = B
It is used to create "smooth" gradients. As a home work, find the formula for a "quadratic" interpolation :-)
To the original poster, from the jpeg it looks like you inserted the blank rows, and that you fill out the first interpolation. Is you problem in the next series or what?
Marco (I am a physicist, for whoever who was wondering)
mstraf at 2007-11-11 17:30:36 >
# 5 Re: Need a Macro plz !
well i managed to do a macro ... to insert blank rows ...
the main problem that i want to interpolate the whole new blank rows !
i want a macro to do that .... coz i have huge data
the macro must interpolate every nine blank rows... coz i tried 2 interpolate the whole file ... it change the original values
i wish i could explianed it well
# 6 Re: Need a Macro plz !
marco,
As homework, BITE ME! :p
interpolate ( P ) Pronunciation Key (n-trp-lt)
v. interpolated, interpolating, interpolates
v. tr.
To insert or introduce between other elements or parts.
To insert (material) into a text.
To insert into a conversation. See Synonyms at introduce.
To change or falsify (a text) by introducing new or incorrect material.
Mathematics. To estimate a value of (a function or series) between two known values.
v. intr.
To make insertions or addition
Did I mention that I'm an eclipseophysicist?
LOVE YA!
L
Laurel at 2007-11-11 17:32:33 >
# 7 Re: Need a Macro plz !
so either interpolate or insert ... some one found a solution ?
it 'll save my time ... about 3 days of work ...
i wish some one help me here :)
# 8 Re: Need a Macro plz !
I have tested this with some sample data and it works. I haven't tried to optimize it; I left as much detail in the code so you could see how it works.
Sub Interpolate()
'Option Explicit
Dim DataIn() As Variant
Dim DataOut() As Variant
Dim i As Long
Dim j As Long
Dim k As Long
Dim maxData As Long
Dim delta3 As Single
Dim delta4 As Single
Dim WSName As Worksheet
ReDim DataIn(6000, 4)
'define the active sheet as the input worksheet
Set WSName = ActiveWorkbook.Worksheets("DataIn")
'load worksheet range into input array - this is very fast
DataIn = WSName.Range("a1:d10")
'get the size of the input array
maxData = UBound(DataIn, 1)
'define the size of the output array
ReDim DataOut(1 To 10 * maxData + 1, 1 To 4)
'loop thru the input array
For i = 1 To maxData - 1
'define the subscript for the ourput array
j = 10 * (i - 1) + 1
'copy columns 1 & 2 from the input array to the output array
DataOut(j, 1) = DataIn(i, 1)
DataOut(j, 2) = DataIn(i, 2)
'get the interpolation step size for columns 3 and 4
delta3 = (DataIn(i + 1, 3) - DataIn(i, 3)) / 10#
delta4 = (DataIn(i + 1, 4) - DataIn(i, 4)) / 10#
Debug.Print i; j; delta3; delta4
'loop thru the interpolation steps
For k = 1 To 10
'define the interpolated values for columns 3 & 4
'and store in the output array
DataOut(j + (k - 1), 3) = DataIn(i, 3) + (k - 1) * delta3
DataOut(j + (k - 1), 4) = DataIn(i, 4) + (k - 1) * delta4
Next '[k] end of interpolation loop
Next '[i] end of input values
'copy columns 1 & 2 from the input array to the output array
i = maxData
j = 10 * (i - 1) + 1
DataOut(j, 1) = DataIn(i, 1)
DataOut(j, 2) = DataIn(i, 2)
DataOut(j, 3) = DataIn(i, 3)
DataOut(j, 4) = DataIn(i, 4)
'define the active sheet as the output worksheet
Set WSName = ActiveWorkbook.Worksheets("DataOut")
'load output array to worksheet range - this is very fast
WSName.Range("A1").Resize(10 * maxData + 1, 4).Value = DataOut
End Sub
# 9 Re: Need a Macro plz !
thanks for your help
But it didn't wok on my data
I posted my problem in another forum ...& some one helped me
Option Explicit
Sub InsertBlankRows()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim r As Long
Dim Rng As Range
Dim lastrw As Long
Dim Ar As Range
Dim StepValue1
Dim StepValue2
Dim Ar1 As Range
Dim AR2 As Range
numRows = InputBox("How many Rows")
lastrw = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range(Cells(1, "A"), Cells(lastrw, "A"))
For r = Rng.Rows.Count To 1 Step -1
Rng.Rows(r + 1).Resize(numRows).EntireRow.Insert
Next r
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
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
Next
End Sub
i couldn't identify what is wrong with your code... but i really apperciate your help
thanks
