Script works in Debugger but doesn't when Macro runs

[sqeekypotato]

Hi all,

I'm very new to VB and macro programing and I am trying to do a very simple Macro for merging data. My problem is that the macro works when I am stepping through in debugger, but does not work when I am in excel and I use the shortcut key (crtl + m) to run the macro. What happens when it doesn't work properly is it selects rows 2 and 3 and tries to paste them to the new workbook and I get an error saying the cells are not the correct type. I don't want it to do this because rows 2 and 3 are titles and have nothing to do with the data I am moving. As I said before, when I open VBE and step through the code it works just fine. The error only happens when there is no data on the initial work sheet.

Thanks

Geoff

Please Login or Register  to view this content.

[Leith Ross]

Hello sqeekypotato,

Welcome to the Forum!

Whenever you encounter an error in your code, you should include the error number, the error description, and where in the code the error occurred. My guess is that you have the source worksheet selected when you run the macro and that is why it runs fine in debug.

In VBA if you don't declare or prefix an range object with the worksheet it belongs to then the active sheet of the active workbook is assumed. It would be easier and faster to help you find a solution if you posted the all the code for the macro. If you can post the complete workbook, that would be even better.

[sqeekypotato]

Hi Leith,

Thanks for your help on this. I posted the majority of the macro. I left out the Sub and End Sub, but I will post the whole thing again just to be sure. I'll give a more detailed explination of what I am trying to do. There will be 2 possibilities. If the macro is run and there is no data on the sheet (aside from the titles) and if the macro is run and there is data on the sheet. Right now the macro works just fine if there is data on the sheet and it works just fine in the debugger if there is no data on the sheet, but screws up when the macro is run with no data on the sheet. Basically what it is supposed to do is start, then do a search until it comes across the first empty cell in the first row and then step up one cell. If the active cell says "inspector", because there is no data on the sheet then it is to close (Inspector is in a title bar and the empty cell search will end up here if there is no data on the sheet). If it says anything else, it means there was data on the sheet and it is to run the rest of the script, which is basically selecting the rows and pasting them onto another workbook. It has to determine in the new workbook where the first blank line is so it does a search for that. What screws up when I run the macro in the blank sheet is that the sheet doesn't close, it opens the workbook on the network drive and tries to paste the title bars onto that file which gives me an error "run time error - 13" and then "type mismatch" beneath that. I'm pretty sure that the error is becuase it is trying to paste text into a column that is reserved for a date. However, I don't want the macro to get this far, it should have close the original workbook and not even opened up the one on the network drive. It is ignoring the application.quit command and I can't figure out why. I will post the xls file if you need me to but it refers to another workbook on my network so you won't be able to see the whole thing in action anyways. Here is the complete code. Thanks again for the help.

Please Login or Register  to view this content.

[jrdnoland]

Try to add some error handling. The simplest would be to add:

Please Login or Register  to view this content.

at the beginning of the sub. That may allow you to bypass the error that is being generated.

[romperstomper]

Just test A3 for data before you start:

Please Login or Register  to view this content.

[Jack in the UK]

This looks like one of those problems with recoreded VBA codes and edits / adding codes

Your at work on a network, running UNC, i find this can trip up best is to test the file exists in the location and error handel (just my opinion)

Teh code runs arround Cells A3 in two workBooks - this seems to be your problem, and needs addressing

[sqeekypotato]

jrdnoland and romperstomper, thanks for your help.

jrdnoland - I tried this and it stopped the error from happening but it also cut out the titles on the original sheet which I need to be there so it is still not working perfectly

romperstomper - Your suggestion stopped the moving of the title bars when the sheet is empty but the application did not exit. I understand why this is, my appliction.quit command occurs before my End Sub. I'm going to try to move it out of the Sub/End Sub section and see if this works. If it doesn't, do you have any suggestions on how to still have the application quit at the end? I was trying to get the GoTo function to go to a line of code but I don't think that is what it is supposed to do.

Thanks again all for the help

[sqeekypotato]

Hi Jack,

I didn't really understand a lot of what you meant here, having said that, I don't know if the network is the problem because the code works fine when it is actually coping/pasting data across the network. It doesn't work when it is not supposed to do that and just exit out of the sheet/excel.

[romperstomper]

If you want it to quit if A3 is empty, then change the second line to:

Please Login or Register  to view this content.

[sqeekypotato]

Hi all,

Got it working! Thanks to everyone for their help. Here is the final product that does what I need it to if anyone is interested.

Please Login or Register  to view this content.

[sqeekypotato]

If you want it to quit if A3 is empty, then change the second line to:

Please Login or Register  to view this content.

I tried this and it would still run the rest of the code for some reason. But your If Len statement was the help I needed to get it running. Thanks!