macro not working as expected, need someone good at debugging macro's

**dmcgov** · 12-21-2015, 05:02 PM

HELLO ALL,

So I have this macro that reads a sheet (named A through FFF), which basically copies each sheet to a "master" page, performs some calculations (like adding items up or deleting rows) and then copies the master to a final sheet (called "big master"). with me so far? when i run the macro on just a few pages (like A-F), the routine works like a champ. but when i run in on my big list (A-FFF), then it fails in certain pages (see list below). I have tried recreating the source pages to no avail. i just cant figure out why its doing what its doing. i have included the workbook and most of the problematic sheets (left A which works perfectly, cant upload all the sheets as it makes the file go above 1Mb). the macro that i run is called "grand finale" which isn't so grand now that its not working as expected. i need someone with a critical eye that can deduce what is wrong with my source pages.

Sheets that dont calc correctly:
Sheets T, W, Y, Z, AA, HH, II, UU, VV, ZZ, DDD, EEE, FFF (left this one off so that i could get file size under 1mb)

Estimate Test upload.xlsm

please rename "estimate test upload" to "estimate test", otherwise the macro will fail. thanks

**skywriter** · 12-21-2015, 05:15 PM

Rather than all that explanation, it might be more helpful to just include the code, tell us which line it fails on and what the error message is.

**dmcgov** · 12-21-2015, 05:20 PM

No error message as the routine runs through the paces normallly. so no error's at all. which is why i included the workbook, its the only way to see what is not happening. when you look at exhibit A (fixture 001) it correctly puts all the info on big master, but then it fails on the sheets listed in the OP.

so can anyone help me?

**Leith Ross** · 12-21-2015, 06:43 PM

Hello dmcgov,

There is quite a bit of code that needs to be looked at. I wouldn't expect anyone to respond right away. It may take a day or two for someone to locate the problem.

**dmcgov** · 12-22-2015, 08:00 AM

ok, ill wait. i just cant figure out why its not working. most of the tabs are good. but the ones that dont work. i just dont know.

thanks for all your help.

**dmcgov** · 12-22-2015, 10:38 AM

so one place the macro fails is during this code:

Please Login or Register  to view this content.

what this section of the code does is to take all of the rows from Master and insert it into BigMaster. but it only copies one column of data, instead of the whole range.

any ideas?

**dmcgov** · 12-22-2015, 10:48 AM

so one place the macro fails is during this code:

Please Login or Register  to view this content.

what this section of the code does is to take all of the rows from Master and insert it into BigMaster. but it only copies one column of data (this shows up in certain pages), instead of the whole range. i can list the pages that fail this way if necessary.

any ideas?

**jaslake** · 12-22-2015, 10:56 AM

Hi dmcgov

You're missing a couple of dots (.) in these two lines...could be part of the problem.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**dmcgov** · 12-22-2015, 11:02 AM

I made the change that you requested and still the same problem with this part of the macro. it only copies one column and not the entire range.

**nilem** · 12-22-2015, 11:12 AM

Hi dmcgov,
try this

Please Login or Register  to view this content.

**dmcgov** · 12-22-2015, 11:25 AM

nilem, gold star for you. that fixed one of the problems with the macro. now on to the next one. i will detail that in the post below.

**dmcgov** · 12-22-2015, 11:33 AM

ok so the next part of the macro needs to copy formulas correctly.

here is the offending code (there is a lot of it). i will detail what fails during the macro running.

Please Login or Register  to view this content.

when it copies the formulas from the formulas tab, it fails on these lines of code

Please Login or Register  to view this content.

instead of giving the fixture number and name, it comes in as #N/A for both columns.

any ideas?

**nilem** · 12-22-2015, 12:44 PM

What is lookupABC123 and lookupROOMS - the named range? variable?

**dmcgov** · 12-22-2015, 12:47 PM

its found on the formulas sheet. its a named range. as far as i can tell, it should pull the names and numbers over like it does on most of the sheets.

thanks for looking at this.

**nilem** · 12-22-2015, 01:05 PM

I tested this code in your file

Please Login or Register  to view this content.

and it works for me

**dmcgov** · 12-22-2015, 01:32 PM

@nilem, now when i run "grand finale", it leaves off the fixture number (havent fixed the room names yet).

any idea on why the column A is blank? i will put in the whole macro so you can run that against the workbook.

Please Login or Register  to view this content.

dont forget to rename the workbook, it should be "estimate test" and not "estimate test upload".

**dmcgov** · 12-22-2015, 02:14 PM

my general thinking is that there must be something corrupt on the individual sheets though i cant point out where this would be. the macro runs fine on 75% of the sheets. so if i take sheet z and copy it to zz, then it works just fine. if i restore the old zz, then it fails. not sure of how to proceed.

**nilem** · 12-23-2015, 01:17 AM

Hi dmcgov,
I do not really understand the essence of your macro, but try this

Please Login or Register  to view this content.

Have you observed Falkon9 landing in Florida? Probably, it is amazing.

**dmcgov** · 12-23-2015, 08:07 AM

@nilem, yup, same problem, either blank in column A or NA in column B. any thoughts?

**nilem** · 12-23-2015, 09:14 AM

Look at the Sheet "W" cell D5. There is space (gap) at the end of the word "Exhibit W", remove this space. Do the same thing with a sheet "AA" and "HH"

**dmcgov** · 12-23-2015, 09:40 AM

Wow, glad you have a critical eye! Gold Stars for you... i'll be marking this as solved.

macro not working as expected, need someone good at debugging macro's

LinkBack

Thread Tools

Rate This Thread

Display

macro not working as expected, need someone good at debugging macro's

Re: macro not working as expected, need someone good at debugging macro's

Re: macro not working as expected, need someone good at debugging macro's

Re: macro not working as expected, need someone good at debugging macro's

Re: macro not working as expected, need someone good at debugging macro's

Re: macro not working as expected, need someone good at debugging macro's

Re: macro not working as expected, need someone good at debugging macro's

Re: macro not working as expected, need someone good at debugging macro's

Re: macro not working as expected, need someone good at debugging macro's

Re: macro not working as expected, need someone good at debugging macro's

Re: macro not working as expected, need someone good at debugging macro's

Re: macro not working as expected, need someone good at debugging macro's

Re: macro not working as expected, need someone good at debugging macro's

Re: macro not working as expected, need someone good at debugging macro's

Re: macro not working as expected, need someone good at debugging macro's

Re: macro not working as expected, need someone good at debugging macro's

Re: macro not working as expected, need someone good at debugging macro's

Re: macro not working as expected, need someone good at debugging macro's

Re: macro not working as expected, need someone good at debugging macro's

Re: macro not working as expected, need someone good at debugging macro's

Re: macro not working as expected, need someone good at debugging macro's

Thread Information

Users Browsing this Thread

Similar Threads

Macro template good saved but running macro with imported data problem

[SOLVED] Help debugging macro

Specific line of my macro isn´t working as expected

[SOLVED] Debugging a macro

MACRO debugging but working in specific istances

[SOLVED] Macro debugging

Help please in debugging this macro

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions