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

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" wrote in message
...

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.


--
smurray444
------------------------------------------------------------------------
smurray444's Profile: http://www.excelforum.com/member.php...o&userid=28956
View this thread: http://www.excelforum.com/showthread...hreadid=506706