Correcting an extrapolation macro

Dear all,

Someone has designed a macro for me which should perform a linear extrapolation on gaps in my dataset.

However, it appears to work across the rows as opposed to down the columns. I require it to work on columns F-I and R-U (inclusive) as opposed to the whole dataset.

Would anybody be able to correct the macro below to achieve this?


Sub Pfil()

Dim i As Long
Dim r As Long
Dim er As Long
Dim sc As Long
Dim ec As Long
Dim src As Range
Dim dest As Range

'Start Row
r = 2
'Last row
er = Range("a65536").End(xlUp).Row - 1
'Start column (G) = first column + 1
sc = 7
'End column (L) = last column - 1
ec = 12

'Do all rows of data
For i = 1 To er

'If first Col is blank, fill
If Cells(r, sc - 1) = "" Then
Set src = Range(Cells(r, sc), Cells(r, ec))
Set dest = Range(Cells(r, sc - 1), Cells(r, ec))
src.AutoFill Destination:=dest, Type:=xlFillSeries
End If
'If last Col is blank, fill
If Cells(r, ec + 1) = "" Then
Set src = Range(Cells(r, sc), Cells(r, ec))
Set dest = Range(Cells(r, sc), Cells(r, ec + 1))
src.AutoFill Destination:=dest, Type:=xlFillSeries
End If
r = r + 1
Next i

End Sub


I hope someone is able to tackle this for me!

Many thanks for your help,
Steve M

Steve,

Post your messages ONCE, to ONE group. You are missing out on replies, and just complicating things
in general.

Bernie
MS Excel MVP


"smurray444" <[email protected]> wrote in message
news:[email protected]...
>
> Dear all,
>
> Someone has designed a macro for me which should perform a linear
> extrapolation on gaps in my dataset.
>
> However, it appears to work across the rows as opposed to down the
> columns. I require it to work on columns F-I and R-U (inclusive) as
> opposed to the whole dataset.
>
> Would anybody be able to correct the macro below to achieve this?
>
>
> Sub Pfil()
>
> Dim i As Long
> Dim r As Long
> Dim er As Long
> Dim sc As Long
> Dim ec As Long
> Dim src As Range
> Dim dest As Range
>
> 'Start Row
> r = 2
> 'Last row
> er = Range("a65536").End(xlUp).Row - 1
> 'Start column (G) = first column + 1
> sc = 7
> 'End column (L) = last column - 1
> ec = 12
>
> 'Do all rows of data
> For i = 1 To er
>
> 'If first Col is blank, fill
> If Cells(r, sc - 1) = "" Then
> Set src = Range(Cells(r, sc), Cells(r, ec))
> Set dest = Range(Cells(r, sc - 1), Cells(r, ec))
> src.AutoFill Destination:=dest, Type:=xlFillSeries
> End If
> 'If last Col is blank, fill
> If Cells(r, ec + 1) = "" Then
> Set src = Range(Cells(r, sc), Cells(r, ec))
> Set dest = Range(Cells(r, sc), Cells(r, ec + 1))
> src.AutoFill Destination:=dest, Type:=xlFillSeries
> End If
> r = r + 1
> Next i
>
> End Sub
>
>
> I hope someone is able to tackle this for me!
>
> Many thanks for your help,
> Steve M
>
>
> --
> smurray444
> ------------------------------------------------------------------------
> smurray444's Profile: http://www.excelforum.com/member.php...o&userid=28956
> View this thread: http://www.excelforum.com/showthread...hreadid=506706
>

Ok - appologies about that.