Run time error 1004 - Can't run VB macro cos of syntax error

goose77

New Member
Joined
Aug 6, 2004
Messages
6
All

I am new to the msg board and I am about to toss the monitor out the window!

I have a macro which loops thru a list of cost centres in one workbook, copies each cost centre to a template workbook ("causal tracksv2) and then refreshes the workbook twice (this is because we use an Excel add in, the data cube TM1, which must sometimes send on first refresh and then receive data on second refresh).

When the code gets to the first refresh, the run time error in the subject of this posting occurs. When I hit debug and then carry on as normal without changing anything, the macro continues running as it should.

The run time error happens every time there is a new cost centre, and there are alot of them! The offending code seems to be the 2nd last line in:

While Cells(i, 1) <> ""
CC = Cells(i, 1)
Workbooks("causal tracksv2").Activate
Cells(1, 1).Select
ActiveCell.FormulaR1C1 = CC
Workbooks.Application.Calculate
Workbooks.Application.Calculate

Any help would be appreciated!

Thanks!

:oops:
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Re: Run time error 1004 - Can't run VB macro cos of syntax e

If you are saying that the offending line is this...
Code:
Workbooks.Application.Calculate
Then that is a built-in method of XL, and has been notorious for causing problems. What function are you copying to the new cell (ie what is the value of CC)?
 
Upvote 0
Thanks for the reply, TommyGun

That is the offending line of code

CC is a text string, "OACSGE"
 
Upvote 0
Re: Run time error 1004 - Can't run VB macro cos of syntax e

Why are you forcing XL to calculate, and another question...why are you doing that twice?
 
Upvote 0
Re: Run time error 1004 - Can't run VB macro cos of syntax e

It should calc twice to send into the TM1 data cube with the send formulae, and then receive from the data cube with the receive formulae. I just tried making it calc only once but it didn't make a difference, the error still appears.

It needs to calc because that is how you get the spreadsheet to talk to the data cube. The TM1 add in has TM1 excel formulae which, when you refresh the workbook, will get info from the cube.
 
Upvote 0
Re: Run time error 1004 - Can't run VB macro cos of syntax e

Is there no way to interface with the TM1 add-in directly through VB and have it refresh that way?
 
Upvote 0
Re: Run time error 1004 - Can't run VB macro cos of syntax e

Also try changing...
Code:
Workbooks.Application.Calculate
To...
Code:
Application.Calculate
 
Upvote 0
Re: Run time error 1004 - Can't run VB macro cos of syntax e

Thanks for the time

There is no way to interface directly. I asked the makers of the software for their VB password and they refused, and not keen to go against their wishes!

Also, tried changing that line but had no joy, error still occurs. The weird thing is that as soon as it has bugged out, I hit debug and then F8 and it is on its way. Makes me think it is an Excel issue?
 
Upvote 0
Re: Run time error 1004 - Can't run VB macro cos of syntax e

Also tried changing

Cells(1, 1).Select
ActiveCell.FormulaR1C1 = CC

to

Cells (1,1).Value = CC

And still no joy

My ambiguity quote is starting to irritate me!
 
Upvote 0
Re: Run time error 1004 - Can't run VB macro cos of syntax e

I wasn't talking about actually using their code, I was talking about referencing it. Ask the people that made it if you can call their methods directly from VB without having to use worksheet formulas.
 
Upvote 0

Forum statistics

Threads
1,212,928
Messages
6,110,734
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top