VBA Code works by stepping through, not by running

[Guest]

Hi,

I have a macro that opens a file, reads data and performs calculations and
returns the results to a main sheet. I run through monthly files for several
years. I have been running a similar process w/o incident. For some reason
my code returns results if I Step through via F8, but not if I let it run
full steam. I get back zilch. I'm no expert, but can someone explain to me
why whatever variable assignment I have works at the slow speed but not at
normal speed? I'm really stumped since I've done similar actions many times
over and the core part of the code is the same?

 

[crispbd]

If you post your code we can comment

 

[HotRod]

My guess is something to do with opening the file, does the file get opened
(time) before you try to process it? If you want to find the problem and
some break points and hit F5, then see which break points it makes it to
without error. I would start near the top and slowly move the break point
lower in the code. You may also want to watch some values to make sure you
re getting what's expected.

 

[Guest]

Thank you both for responding. One of the problems is that when the file
opens, the code:

'weekly wbk with data
dataBk = sFile & edate & ".xls"

Workbooks.Open Filename:=sDir & dataBk
Workbooks(dataBk).Activate
Sheets(1).Activate

'last row of data
lRow = Range("B" & Rows.Count).End(xlUp).Row

the variable for lRow does not get the proper value. It should be 1509 in
the sample month I'm running, but comes back as 1. There is data in Col B
from Row 11 to 1509. (The 1509 can change each month, hence the bottom up
look)

Also, when the monthly file opens, it doesn't seem to become active in the
foreground - even after I tried the .activate - allowing the lRow variable to
populate.

This is really frustrating since I have done exactly the same type of
routine on other data files.

Thanks.

jbl

 

[Guest]

for some reason the line

Workbooks(dataBk).Activate

does not switch between books so the variables don't pick up their proper
values. Any clues? (the book is open, it just won't switch to it)

 

[Guest]

I've noticed that if I change the Sheets(1).activate to a sheet name it
apears to work. Still checking. Any ideas?

 

[Tom Ogilvy]

I have never seen an instance of the workbook just opened not being the
active workbook. However, if it isn't, then any unqualified code pointing
to the activesheet will certainly operate on the wrong sheet.

Another problem may stem from where the code is located. Is this code
located in a sheet or the Thisworkbook module. If so, then try moving it to
a general module. If you use a button to execute it, just call from the
buttons click event rather than have the code located in the event.

 

[Guest]

I'm not sure why this macro ended up giving me such a hard time. I changed
the sheet references from (n) to their "names" and then it worked. There
must be a disconnect somewhere in the code and I'll try to find it.

Thanks all.

jbl