Can't find my error

[ajc5382]

Hi,

I have the following code that is used to count a variable number or rows of raw data, then extend formulas to the last raw data row in columns M to Q in my spreadsheet.

The error(User/Object defined error) occurs below in the line Selection.AutoFill Destination....etc

Thanks for your help!

Private Sub ExtendFormulas()
   Application.Calculation = xlCalculationManual
     Dim LastRow1 As Long
     LastRow1 = (Range("A" & Rows.Count).End(xlUp).Row) - 1
          Range("M2:Q2").Select
            Selection.AutoFill Destination:=Range("M3" & ":Q" & LastRow1)
   Application.Calculation = xlCalculationAutomatic
End Sub

 

[AlphaFrog]

Try this.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] ExtendFormulas()
   Application.Calculation = xlCalculationManual
     [color=darkblue]Dim[/color] LastRow1 [color=darkblue]As[/color] [color=darkblue]Long[/color]
     LastRow1 = Range("A" & Rows.Count).End(xlUp).Row - 1
          Range("M2:Q2").AutoFill Destination:=Range("M2" & ":Q" & LastRow1)
   Application.Calculation = xlCalculationAutomatic
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

The destination should include the source cells M2:Q2

 

[ajc5382]

Try this.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] ExtendFormulas()
   Application.Calculation = xlCalculationManual
     [color=darkblue]Dim[/color] LastRow1 [color=darkblue]As[/color] [color=darkblue]Long[/color]
     LastRow1 = Range("A" & Rows.Count).End(xlUp).Row - 1
          Range("M2:Q2").AutoFill Destination:=Range("M2" & ":Q" & LastRow1)
   Application.Calculation = xlCalculationAutomatic
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

The destination should include the source cells M2:Q2

Worked great! My issue now is that when I add my raw data(cols A-L), my macros automatically run. Do you know how to make it so they only run when I push my buttons? Thanks for your help.

 

[AlphaFrog]

Worked great! My issue now is that when I cut in past my raw data, my macros automatically run. Do you know how to make it so they only run when I push my buttons? Thanks for your help.

You're welcome.

The followup question is too vague for me to answer. How are you cutting and pasting? Do you have any event procedures like a Private Sub Worksheet_SelectionChange(ByVal Target As Range) ?

 

[ajc5382]

I download raw data monthly, which varies only in rows of data and paste into columns A to L. Columns M2 > Q(depends on how many rows of raw data) have formulas that use the raw data to form column specific information.

No event procedures...I just have a simple sub to clear my formula cells(L2:Q(depends on rows of raw data), and then one that extends formulas down to the last row of data.

I'd like to be able to paste my data in, and only have it calculate if I push my button. Right now, I paste my data in and it begins calculating automatically.

 

[AlphaFrog]

I download raw data monthly, which varies only in rows of data and paste into columns A to L. Columns M2 > Q(depends on how many rows of raw data) have formulas that use the raw data to form column specific information.

No event procedures...I just have a simple sub to clear my formula cells(L2:Q(depends on rows of raw data), and then one that extends formulas down to the last row of data.

I'd like to be able to paste my data in, and only have it calculate if I push my button. Right now, I paste my data in and it begins calculating automatically.

You clear all the formulas before you paste?

Show your Sub that clears the formulas.

The Sub ExtendFormulas uses existing formulas in M2:Q2. So those are not cleared.

You can set formula calculations to Manual in the Tools\Options menu. Then use the F9 keyboard key to trigger a calculation.

 

[ajc5382]

You clear all the formulas before you paste?

Show your Sub that clears the formulas.

The Sub ExtendFormulas uses existing formulas in M2:Q2. So those are not cleared.

You can set formula calculations to Manual in the Tools\Options menu. Then use the F9 keyboard key to trigger a calculation.

Sub ClearContents()
    Dim LastRow As Long
    LastRow = (Range("A" & Rows.Count).End(xlUp).Row) - 1
    Range("M3:" & "Q" & LastRow).ClearContents
End Sub

I have a button calling this sub, then after its clears, calls a sub to extend my formulas. Also - not sure why this is happening, but whenever I click my buttons they either keep getting bigger or the text in them keeps getting small.

Thanks for helping!