Need a Macro plz !

[geophysicist]

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

[Laurel]

What are you trying to DO with those values?

[geophysicist]

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

[Laurel]

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

[mstraf]

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)

[geophysicist]

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

[Laurel]

marco,

As homework, BITE ME!

in·ter·po·late ( P ) Pronunciation Key (n-tûrp-lt)
v. in·ter·po·lat·ed, in·ter·po·lat·ing, in·ter·po·lates
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

[geophysicist]

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

[peaches]

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

[geophysicist]

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