Error 1004 Application defined or object defined error

[Peter Rouse]

I must be getting old.
I have a userform setup to enter data into a worksheet
Whee the user hits the button to add the data, I get the error 1004
Application defined or Object defined error
It is on the line Set LastRow

Private Sub CommandButton1_Click()
Dim LastRow As Object

Set LastRow = Sheet1.Range("a65536").End(x1Up)

....end sub

Now I am sure this has always worked in the past, so Is it me or am I really missing a plank or two?

Thanks

Peter

Thanks

Peter

 

[Joe4]

It should be:

Set LastRow = Sheet1.Range("a65536").End(xlUp)

not

Set LastRow = Sheet1.Range("a65536").End(x1Up)

 

[Peter Rouse]

Thanks jmiskey, I just knew it was somehting I had done, but kept looking at the wrong bit of code


I Promise to remove blinkers
I promise to remove.....

Peter

 

[Fidget321]

Hi All,

I know this thread is old, but i am getting the exact same error.
My macro gets its data from a database and just gets it into a nice format for users.

Now this has been working for years and years, but now all of a sudden, I am getting the above mentioned error at the following code in Red:

Sheets("Sheet2").Select
Cells.Select
Selection.Delete

Sheets("Sheet1").Select
Range("C12").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Selection.Copy
Sheets("Sheet2").Select
Cells.Select

Please can someone just tell me if there is something wrong with this picture?

Thanks

 

[RoryA]

Is there definitely a query table there, and is the worksheet protected in any way?

 

[Fidget321]

Is there definitely a query table there, and is the worksheet protected in any way?

Thanks for the reply, to be horribly honest, I am not a big Excel/VB expert that is why i sang Hallelujah when i found this forum.


This is what it's supposed to be doing:

A local sql job processes data and just opens the excel file containing this macro. (lets call it file A) gets data from the database, gets it in a better format and saves it as file B


The security settings is set to lowest because we had issues relating to this in the past.


I am not too sure how to see if the Query table is present, how can i see this?

Thanks a mil - any assistance is greatly appreciated.

 

[RoryA]

If you select cell C12 on sheet1 and right-click it, is there an option to refresh data and, if so, can you actually refresh it?

 

[Fidget321]

Thanks Rorya,

No there is no option to refresh data or anything to be honest.
This is very strange, do you have any ideas why this would have changed all of a sudden? - security updates installed or something?

Thanks

 

[RoryA]

I would guess it has probably been copy/pasted over so that the link is no longer dynamic, or it has simply been moved to another location in the sheet?

 

[Fidget321]

I'm not too sure either, I am the only one who looks after this process and i didn'c change anything. But thanks a mill.

At least i know now where to start fixing this issue.