VB Macro

  • Thread starter Thread starter ehtcpa
  • Start date Start date
E

ehtcpa

I have a macro that is generating Excel files. When the macro runs, it saves
two different tabs in the workbooks as values. It needs to test and wait
until the formulas that are pulling amounts from another database are
correctly populated with the amounts.

Right now, I am getting N/A# instead of amounts for some, but not all, of
the files. I think the problem is that the formulas have not completed
pulling the amounts before the macro pastes that tab as zeros. (N/A# is what
appears when the formulas have not completed calculating - once they have
completed, the N/A# is replaced by an amount.)

I am a complete novice to VB. Someone else wrote the macro; one of our
company's IT people has helped me change it, but the errors are still popping
up.

What we have tried includes:

To delay the paste values until sufficient time has elapsed to perform the
calculation:

iDelay = Timer + 120
Do While Timer < iDelay
DoEvents
Loop

To attempt to test the cell for numerical amounts vs. the N/A# error.

bDelay = False
Sheets("Beg Bal - Alea").Activate
Do While bDelay = False
If Not CLng(Range("D15").Value) Then
DoEvents
Else
bDelay = True
End If
Loop

Neither of these work. For example, sometimes companies A11 or A98 or A26
will have an error message and sometimes they won't.

I have over 200 files to generate, therefore I really need this macro to
work consistently and let the calculations happen before it saves values.

Thanks for any help.
 
I'd try dumping all the stuff you tried and just replace it with:

application.calculate

Your code will wait until excel recalcs before continuining.

If you're using some kind of query instead of just plain old formulas, then make
sure that the backgroundquery is set to false--either manually or in code.

Rightclick on your data|query range and select Data Range Properties.
Try changing the "enable background refresh" to off.

In code, something like:
worksheets("Sheet1").QueryTables(1).Refresh Backgroundquery:=false
 
Dave -

Thanks for the suggestion; the application.calculate evidently did not work.
When I tried it (after dumping all the other code as you suggested), all of
the files generated had the N/A# errors.

I don't have a query per se - these are formulas - but they are special
formulas to pull data from our consolidation database, which is MIS Alea.

The formulas look something like this:

=dbgetc(server,cube,year,period,scenario,level,co_num,intco,acct,value)

When I open an Excel file that is pulling a lot of data from the database, I
can see that it is updating buffers and calculating before it actually
populates the amounts.

Not 100% sure what you are talking about with the backgroundquery - the
macro does not display what is happening if that is what you are referring to
(told you I was a novice with VB).

I appreciate the help and please have a Happy New Year!
 
I don't know how your =dbgetc() function works, but maybe...

Depending on the version of excel that you're using, you could try:

application.calculatefull
or
application.CalculateFullRebuild

If that doesn't work, you could try:

Select all the cells on the worksheet
edit|Replace
what: = (equal sign)
with: =
replace all

In code:

with activesheet
.cells.replace what:="=", replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
end with
 
Dave -

This one did not work either. I will not be working on this again until
Wednesday, so I will keep you posted with what comes next.

I really appreciate the help. Have a Happy New Year!

Regards,

Emily
 
There were multiple suggestions there.

Which one didn't work? Did you try the other ones?

(Even though I'm out of suggestions.)
Dave -

This one did not work either. I will not be working on this again until
Wednesday, so I will keep you posted with what comes next.

I really appreciate the help. Have a Happy New Year!

Regards,

Emily
Click to expand...
 
Back
Top