Fill down Error

[J.W. Aldridge]

Normally, my code works fine if I have a few rows of data. However,
I've found that if there's only information in the first two rows (a-
f), I get an error (Run Time: AutoFill Method of range class failed).
Any way to get the code to fill down to row 2 and stop if there's no
more info and not give me an error?


This is the fill down code. The part that returns the error is the
line 7 - 9.
***********************
Range("F2").Select
ActiveCell.FormulaR1C1 = "Next day"
Range("F2").Select
With ThisWorkbook.Worksheets("2mindex")
Set rngData = .Range("e2:e" & .Cells(.Rows.Count, "e").End
(xlUp).Row)
Set rngFormula = .Range("f2")
rngFormula.AutoFill _
Destination:=.Range(rngFormula, _
.Cells(rngData.Rows(rngData.Rows.Count).Row,
rngFormula.Column))
End With

************************

Here's the entire code just in case....

Sub rearrange_data()
..
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Columns("F:F").Select
Selection.Cut Destination:=Columns("B:B")
Columns("B:B").Select
ActiveWindow.SmallScroll ToRight:=0
Columns("G:L").Select
Selection.Delete Shift:=xlToLeft
Range("F1").Select
ActiveCell.FormulaR1C1 = "Reason"
Range("F2").Select
ActiveCell.FormulaR1C1 = "Next day"
Range("F2").Select
With ThisWorkbook.Worksheets("2mindex")
Set rngData = .Range("e2:e" & .Cells(.Rows.Count, "e").End
(xlUp).Row)
Set rngFormula = .Range("f2")
rngFormula.AutoFill _
Destination:=.Range(rngFormula, _
.Cells(rngData.Rows(rngData.Rows.Count).Row,
rngFormula.Column))
End With
Columns("A:F").Select
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub

 

[Joel]

See if this works. I think the problem is you were wipping out the formula
in F2. I think you headers should be in E1 and E2. Also you may have been
referencing more thean one workbook/worksheet because the workbooks and
worksheet were no specified. this would of been a problem if you had more
than one workbook opened or the sheet you werre modifying was not the active
worksheet.



Sub rearrange_data()


With ThisWorkbook.Worksheets("2mindex")

.Columns("B:B").Insert
.Columns("G:L").Delete
.Range("E1") = "Reason"
.Range("F1") = "Next day"
LastRow = .Cells(.Rows.Count, "e").End(xlUp).Row
Set rngData = .Range("e2:e" & LastRow)
Set rngFormula = .Range("f2")
rngFormula.Copy _
Destination:=.Range("F2:F" & LastRow)
.Rows("1:" & LastRow).Sort _
Key1:=.Range("C2"), _
Order1:=xlAscending, _
Header:=xlYes

End With
End Sub

 

[J.W. Aldridge]

Thanx for your help...

Getting error on this

Compile error : Argument not optional

LastRow =

 

[Joel]

I don't know why bu I'm not getting email when people respond to my postings.
I'm not getting the error. Not sure why. Compare my posting against you
code. I think you may not of copied the code properly.

 

[broro183]

hi all,

I started looking at this when Joel's first post was out in, so my
response is slightly behind &...


I can't see how your original code actually worked because to me it
seems that the column F (that it tries to autofill) is empty due to your
earlier code cutting the column & transferring it. Or, is the autofill
meant to put "Next" or "Reason" all the way down?

I'm not sure if I am putting the info into the correct cells but this
shows the principles of creating a LastRow & writing directly to the
range rather than using Autofill or Copy.


Code:
--------------------
Option Explicit
Sub Modified_Rearrange_data()
Dim LastRow As Long
With ThisWorkbook.Worksheets("2mindex")
.Columns("B:B").Insert
.Columns("F:F").Cut Destination:=.Columns("B:B") 'the original Col E becomes Col B
.Columns("G:L").Delete 'the original Col F:K are deleted
'as Joel has questioned, which cells should these headers be in?
.Range("E1") = "Reason" 'this was originally column D
.Range("F1") = "Next day" 'this was originally column F that _
is moved to B - ie the column is now blank
'Initial check for LastRow (in case data is in all rows of the sheet)
With .Cells(.Rows.Count, "e")
LastRow = IIf(Len(.Value) = 0, .End(xlUp).Row, .Row)
End With
'to ensure that row 2 is considered the last row if no data exists
LastRow = Application.WorksheetFunction.Max(2, LastRow)
''the below provides no result b/c the column is blank
' .Range("F2:F" & LastRow).Formula = .Range("F2").Formula
'### change the string as necessary...
.Range("F1:F" & LastRow).Formula = "=RC1 + 1"
.Range("A1:F" & LastRow).Sort Key1:=.Range("C1"), _
Order1:=xlAscending, Header:=xlYes

End With
End Sub
--------------------


Joel,
I like your comments on the need to explicitly define what is being
referred to & to take the good coding practices one step further I would
suggest Dimming the rngData & rngFormula as ranges (otherwise by default
they become variants) & then setting them equal to Nothing at the end of
the code.


hth
Rob

 

[ryguy7272]

Here are some samples from my personal VBA library. Hope you find something
here that you like...

AutoFill Down:
This is how to do it if you have selected the 2 cells:
Sub Autofill1()
Selection.AutoFill Destination:=Range _
(Selection, Selection.Offset(0, 1).End(xlDown).Offset(0, -1))
End Sub


And this is how to do it if you don't want to select the two cells (assumes
that column A is
otherwise blank)
Sub Autofill2()
Dim myR As Range
Set myR = Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0).Resize(2, 1)
On Error Resume Next
myR.AutoFill Destination:=Range _
(myR, myR.Offset(0, 1).End(xlDown).Offset(0, -1))
End Sub


Fill Down in Column E, Based on Used Range in Column D:
Sub fill()
Dim lastrow As Long
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "D").End(xlUp).Row
Range("E5").AutoFill Range("E5:E" & lastrow)
End Sub


Fill Down Values in Column E, with Some Data Already in Column E, Based on
Number of Rows Filled in Column A
Sub AutoFill()
Dim CountRows As Double
Dim Iloop As Double
'Turn off warnings, etc.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
CountRows = Cells(Rows.Count, "A").End(xlUp).Row
For Iloop = 2 To CountRows
If IsEmpty(Cells(Iloop, "E")) Then
Cells(Iloop, "E") = Cells(Iloop - 1, "E")
End If
Next Iloop
'Turn on warnings, etc.
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub


Fill Down Values:
Sub FillBlanks()
Dim rr, r As Range
Set r = Range("A2:A500")
For Each rr In r
If rr.Value = "" Then
rr.value = rr.offset(-1,0).value
End If
Next rr
End Sub


HTH,
Ryan---