Hoopsah
12-14-2010, 04:20 AM
Hi
I am trying to autofill a column to go down to the last row of data.
However, when I run this procedure it fills an extra 2 lines with the formula and I really can't see what I am missing.
The formula is in cell J5 and it is a simple NETWORKDAYS formula.
Could someone have a look at my code and see what I am neglecting to do.
Any responses welcomed.
Public Sub SubmitDetails()
'
' SubmitDetails Macro
' Macro recorded 11/11/2010 by Gerry McNally
'
Dim pos As Long
Dim NextRow As Long
Dim LastRow As Long
On Error Resume Next
pos = Application.Match(Range("G11").Value, Worksheets("Input Sheet").Columns(2), 0)
On Error GoTo 0
With Worksheets("Input Sheet")
If pos > 0 Then
.Cells(pos, "A").Value = Range("D14").Value
.Cells(pos, "C").Value = Range("D17").Value
.Cells(pos, "D").Value = Range("G17").Value
.Cells(pos, "E").Value = Range("J17").Value
.Cells(pos, "F").Value = Range("D20").Value
.Cells(pos, "G").Value = Range("G20").Value
.Cells(pos, "H").Value = Range("D22").Value
.Cells(pos, "I").Value = Range("D26").Value
.Cells(pos, "K").Value = Range("H26").Value
.Cells(pos, "L").Value = Range("D28").Value
.Cells(pos, "M").Value = Range("H28").Value
Else
NextRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Cells(NextRow, "A").Value = Range("D14").Value
.Cells(NextRow, "B").Value = Range("G11").Value
.Cells(NextRow, "C").Value = Range("D17").Value
.Cells(NextRow, "D").Value = Range("G17").Value
.Cells(NextRow, "E").Value = Range("J17").Value
.Cells(NextRow, "F").Value = Range("D20").Value
.Cells(NextRow, "G").Value = Range("G20").Value
.Cells(NextRow, "H").Value = Range("D22").Value
.Cells(NextRow, "I").Value = Range("D26").Value
.Cells(NextRow, "K").Value = Range("H26").Value
.Cells(NextRow, "L").Value = Range("D28").Value
.Cells(NextRow, "M").Value = Range("H28").Value
.Range("J5").AutoFill .Range("J5").Resize(LastRow - 1)
End If
End With
ActiveSheet.Unprotect
Application.Run "ClearScreen"
ActiveSheet.Protect
End Sub
I am trying to autofill a column to go down to the last row of data.
However, when I run this procedure it fills an extra 2 lines with the formula and I really can't see what I am missing.
The formula is in cell J5 and it is a simple NETWORKDAYS formula.
Could someone have a look at my code and see what I am neglecting to do.
Any responses welcomed.
Public Sub SubmitDetails()
'
' SubmitDetails Macro
' Macro recorded 11/11/2010 by Gerry McNally
'
Dim pos As Long
Dim NextRow As Long
Dim LastRow As Long
On Error Resume Next
pos = Application.Match(Range("G11").Value, Worksheets("Input Sheet").Columns(2), 0)
On Error GoTo 0
With Worksheets("Input Sheet")
If pos > 0 Then
.Cells(pos, "A").Value = Range("D14").Value
.Cells(pos, "C").Value = Range("D17").Value
.Cells(pos, "D").Value = Range("G17").Value
.Cells(pos, "E").Value = Range("J17").Value
.Cells(pos, "F").Value = Range("D20").Value
.Cells(pos, "G").Value = Range("G20").Value
.Cells(pos, "H").Value = Range("D22").Value
.Cells(pos, "I").Value = Range("D26").Value
.Cells(pos, "K").Value = Range("H26").Value
.Cells(pos, "L").Value = Range("D28").Value
.Cells(pos, "M").Value = Range("H28").Value
Else
NextRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Cells(NextRow, "A").Value = Range("D14").Value
.Cells(NextRow, "B").Value = Range("G11").Value
.Cells(NextRow, "C").Value = Range("D17").Value
.Cells(NextRow, "D").Value = Range("G17").Value
.Cells(NextRow, "E").Value = Range("J17").Value
.Cells(NextRow, "F").Value = Range("D20").Value
.Cells(NextRow, "G").Value = Range("G20").Value
.Cells(NextRow, "H").Value = Range("D22").Value
.Cells(NextRow, "I").Value = Range("D26").Value
.Cells(NextRow, "K").Value = Range("H26").Value
.Cells(NextRow, "L").Value = Range("D28").Value
.Cells(NextRow, "M").Value = Range("H28").Value
.Range("J5").AutoFill .Range("J5").Resize(LastRow - 1)
End If
End With
ActiveSheet.Unprotect
Application.Run "ClearScreen"
ActiveSheet.Protect
End Sub