Range Autofill Syntax

[JTBorton]

I have never used the Range.Autofill method, so I am having quite a bit of trouble with it. First I read in several columns of experiment data from a CVS file in to the bottom row (the first empty row) of my worksheet. Then, in the columns to the right of the new data, on the same row, I want to auto fill the formulas from the rows of calculations that directly precede the current row of new data. I hope that makes sense, I'm not always the best at conveying what I want to do.

Here is how I am attempting to code it:
The variables used here are:
LastRow - the last row of the excel sheet which contains data. This is the row where I have just placed my experiment data.
LastColumn - an integer representing the last column of experiment data before the formulas and calculated values.
wkDataSheet - an Excel.Worksheet variable were the experiment data is stored.
StartCalculations - An integer that marks the first column of formulas and data calculations
EndOfCalculations - An integer that marks the last column of formulas and data calculations
I - A simple counting integer
xlDestinationRng - an Excel.Range variable that represents the range of cells to be autofilled.
xlSourceRng - an Excel.Range variable that represents the source range of the calculation formulas. It is directly above the xlDestinationRng range.
rStart - an integer that represents last row of information before I began, or in other words the row directly above where I started adding experimental data. Several rows of data will be filled in before I go back to add in the corresponding formulas.

Run-time error '1004':
Autofill method of Range class failed

'The last column of data and the first column of formulas are separated by an empty column. Add an additional column to that the Loop increments to the correct column to begin its search.    
LastColumn = LastColumn + 1
    I = LastColumn
    'Find the last column of formulas and calculations
    Do
        I = I + 1
        If Trim(wkDataSheet.Cells(4, I)) = "" Then
            EndOfCalculations = I - 1
            Exit Do
        End If
    Loop

    StartCalculations = LastColumn + 1
    Set xlDestinationRng = Range(wkDataSheet.Cells(rStart + 1, StartCalculations), wkDataSheet.Cells(LastRow, EndOfCalculations))
    xlDestinationRng.Select 'This is for me to watch what it does
    Set xlSourceRng = Range(wkDataSheet.Cells(rStart - 3, StartCalculations), wkDataSheet.Cells(rStart, EndOfCalculations))
    xlSourceRng.Select 'This is for me to watch what it does
    [highlight]xlSourceRng.AutoFill Destination:=xlDestinationRng, Type:=xlFillDefault[/highlight]

At code failure the values are:
rStart: 612
LastRow: 663
StartCalculations: 91
EndOfCalculations: 127

-JTBorton
Another Day, Another Disaster

 

[Gavona]

Not sure about your error but why not just use:
xlSourceRng.Copy xlDestinationRng



Gavin

 

[combo]

The destination must include the source range.

This is not satisfied in your sase.

combo

 

[JTBorton]

combo Oh thanks. I did not realize that the destination range needed to include the full source range and the range to autofill. I fixed it with the following code.

StartCalculations = LastColumn + 1
    Set xlDestinationRng = Range(wkDataSheet.Cells(rStart - 3, StartCalculations), wkDataSheet.Cells(LastRow, EndOfCalculations))
    xlDestinationRng.Select
    Set xlSourceRng = Range(wkDataSheet.Cells(rStart - 3, StartCalculations), wkDataSheet.Cells(rStart, EndOfCalculations))
    xlSourceRng.Select
    xlSourceRng.AutoFill Destination:=xlDestinationRng, Type:=xlFillDefault

Gavona Never thought of that. Would have been much easier. But now I know both functions exist and how to use them for future reference.

-JTBorton
If it isn't broken, it doesn't have enough parts yet.