Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-10-2019, 01:35 PM   #1
jalopez453
Newly Registered User
 
Join Date: Oct 2016
Location: Phoenix, AZ
Posts: 18
Thanks: 2
Thanked 0 Times in 0 Posts
jalopez453 is on a distinguished road
Last Row Error

I am trying to auto-fill tow formulas into two columns to the last row on my report. Every things goes thru smoothly until I get to the LastRow formula. I keep getting an error and I don't understand why.

Code:
Dim xlApp As Object
Dim lRow1 As Long
Set xlApp = CreateObject("Excel.Application")

With xlApp
    .Visible = True
    .Workbooks.Open Filename
    'Sheet1
    .Sheets("Sheet1").Select
    .Range("A10").Select
    .ActiveCell.CopyFromRecordset rstCHR

        strFormulas(1) = "=SUMIF(Charity_Line20!$A:$A,E10,Charity_Line20!$D:$D)"
        strFormulas(2) = "=SUMIF(Charity_Line22!$A:$A,E10,Charity_Line22!$G:$G)"
            lRow1 = .Sheets("Sheet1").Cells(.Rows.Count, "U").End(xlUp).Row
            
        .Range("V10:W" & lRow1).AutoFill = strFormulas
If anyone is able to help me, or see where I am coding it wrong, that would be so helpful. My issues is the lRow1

jalopez453 is offline   Reply With Quote
Old 10-10-2019, 01:39 PM   #2
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,608
Thanks: 1
Thanked 625 Times in 618 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Last Row Error

Is the error message "Unable to set the AutoFill property of the Range class"?
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Last edited by June7; 10-10-2019 at 02:27 PM.
June7 is offline   Reply With Quote
Old 10-10-2019, 01:41 PM   #3
jalopez453
Newly Registered User
 
Join Date: Oct 2016
Location: Phoenix, AZ
Posts: 18
Thanks: 2
Thanked 0 Times in 0 Posts
jalopez453 is on a distinguished road
Re: Last Row Error

Run-time error '1004'
Application-defined or object-defined error


I want to copy down the formulas from Row 10 to the last row, and I keep getting this error for the lastrow code.

jalopez453 is offline   Reply With Quote
Old 10-10-2019, 01:51 PM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,238
Thanks: 63
Thanked 1,678 Times in 1,633 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Last Row Error

Just FYI... Cross post...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-10-2019, 02:28 PM   #5
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,608
Thanks: 1
Thanked 625 Times in 618 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Last Row Error

Which line throws error? I don't get that error. I get the one I asked about.

AutoFill is taking content of cell(s) and copying to other cells. The source cell(s) must be included in reference. This simple example worked for me.
Code:
With xlApp
    .Visible = True
    .Workbooks.Open Filename
    .Sheets("Sheet1").Select
    .Range("A10").CopyFromRecordset rs
    lRow1 = .Sheets("Sheet1").Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("F10:F" & lRow1).AutoFill Destination:=.Range("F10:J" & lRow1), Type:=xlFillDefault
End With

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
June7 is offline   Reply With Quote
Reply

Tags
#vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question strSQL = Run Time Error 3075 Syntax Error Missing Operator in qry Expr maxsun08 General 4 07-27-2012 06:39 AM
Error "Run-time error '1004': General ODBC Error D4WNO General 1 07-19-2012 07:42 AM
Error 3734 (share mode error) appearing in error guy- Modules & VBA 2 09-28-2011 02:02 AM
A question about the error message "Run-time error '3061' Too few parameters, Expecte dariyoosh Modules & VBA 7 03-12-2010 06:26 AM
Run time error 3075 - syntax error (missing operator) in query expression puskardas Modules & VBA 8 06-30-2008 07:35 PM




All times are GMT -8. The time now is 08:47 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World