Complicated Macro doesn't work properly if lines are deleted

**BuzzOffSweetheart** · 07-10-2012, 08:17 PM

Hi All,

I really need some help - I am pulling my hair out here..

I have a macro (HExportMacro) that takes data from one sheet (RN) and puts it into another (H).. Then after the user modifies the data in Sheet H, another macro (HImportMacro) puts the modifications from H BACK into the other original sheet (RN).

The problem I am running into is when the user deletes lines from Sheet H, say 3 lines in the middle of the data - The bottom three lines don't get imported back from H sheet into Sheet RN correctly..

I am attaching two examples since it is difficult to explain..

Example 1: After the HExport Macro is ran, and the user has deleted 3 lines and modified the qty's on the botton two lines on the H sheet that were on the RN sheet, before the HImport macro is ran

Example 2: After the HImport Macro is ran, where you see the data for the last 3 lines aren't populating correctly in the RN sheet, (ie.e columns 1, 2, and 36 havent changed and still show the old values from the original RN sheet before the HImportMacro was ran, not the new ones from H)

I would rrrreally appreciate some help!
Thanks guys!

**xladept** · 07-10-2012, 08:33 PM

10,19 is SUPPLIER_A-005 in the H sheet?????????

**abousetta** · 07-10-2012, 08:42 PM

What that makes me think is that the last row is being provided from another variable outside the sub. So by process of elimination, I found that cPreviousRow and cTag are declared but not used (probably just need to be deleted) but also nPreviousRow is used but not declared. Is nPreviousRow shared across subs?

**BuzzOffSweetheart** · 07-10-2012, 08:47 PM

Originally Posted by xladept

10,19 is SUPPLIER_A-005 in the H sheet?????????

Yes.. If I understand what you're asking..

**BuzzOffSweetheart** · 07-10-2012, 08:51 PM

Originally Posted by abousetta

What that makes me think is that the last row is being provided from another variable outside the sub. So by process of elimination, I found that cPreviousRow and cTag are declared but not used (probably just need to be deleted) but also nPreviousRow is used but not declared. Is nPreviousRow shared across subs?

Yeah, this is my first non recorded macro, so my coding is very poor as is my knowledge.. I need to delete the cPreviousRow and cTag..

And you're absoultely correct - nPreviousRow isn't declared.. it should be.. Would that cause the problem I am having?

**abousetta** · 07-10-2012, 08:52 PM

No, it would cause it only if it is shared from another sub. There is a lot of code so I will have to take a second look.

**BuzzOffSweetheart** · 07-10-2012, 09:00 PM

Originally Posted by abousetta

No, it would cause it only if it is shared from another sub. There is a lot of code so I will have to take a second look.

It isn't shared across subs..

I believe it should be

Please Login or Register  to view this content.

As an else statement after the

Please Login or Register  to view this content.

I apologize for being ignorant.. thank you so much for any help!

**abousetta** · 07-10-2012, 09:14 PM

Glad it all worked out. If you are satisfied with the results then please mark the thread as solved.

Also we have a competition this month on Excel Forum, please stop by and click on the like button of the tips you find of benefit.

Good luck.

abousetta

**BuzzOffSweetheart** · 07-10-2012, 09:18 PM

Originally Posted by abousetta

Glad it all worked out. If you are satisfied with the results then please mark the thread as solved.

Also we have a competition this month on Excel Forum, please stop by and click on the like button of the tips you find of benefit.

Good luck.

abousetta

Oh no.. Its not fixed.. That didn't solve the problem, I thought you were going to look at it still... Any other advice? I am willing to donate via paypal for some help.. I am just not knowledgeable enough to fix this problem.. The macro as it stands took me months to build..

**abousetta** · 07-10-2012, 09:20 PM

Sorry I misinterpreted your post. I will try to go over it in more detail tonight.

**xladept** · 07-10-2012, 10:27 PM

My post meant that the first column entry seemed to be what it should?? I'll also look tomorrow but abousetta will have probably solved it by then - good luck!

**BuzzOffSweetheart** · 07-10-2012, 10:48 PM

Originally Posted by xladept

My post meant that the first column entry seemed to be what it should?? I'll also look tomorrow but abousetta will have probably solved it by then - good luck!

I realized after I looked back at it what you meant.. haha..

In my example - I thought I changed the supplier in H sheet, but I didn't.. So the original never changed which is why it was right.. My mistake.

**BuzzOffSweetheart** · 07-11-2012, 01:31 AM

Update: Spent the last 3 hours trying different things to no avail..

I thought it might have been my Loop.. But I tried different Do While statements and added another loop and it's not helping.. Maybe I don't understand how looping works.. because from my code it seems that is should still loop and change the data no matter what gets deleted.. but it's almost like the macro is counting the amount of rows populated in the RN sheet.. and then it is only updating that exact amount of rows the rn sheet originally had when the H sheet data is sent back to RN.

**BuzzOffSweetheart** · 07-11-2012, 02:07 AM

Will someone please help me?

**abousetta** · 07-11-2012, 03:10 AM

I have gone through your code and attempted to streamline where I can and see what the problem. The only thing I see that caught my eye is that nPreviousRow is not defined anywhere even though it used several times. What should this be (e.g. the last row)?

I am uploading the modified version. Also a quick note, with vba you have to declare each variable separately. For example

Please Login or Register  to view this content.

If you put

Please Login or Register  to view this content.

Excel will assign x as a variant and y as an integer.

abousetta

**BuzzOffSweetheart** · 07-11-2012, 10:57 AM

Originally Posted by abousetta

I have gone through your code and attempted to streamline where I can and see what the problem. The only thing I see that caught my eye is that nPreviousRow is not defined anywhere even though it used several times. What should this be (e.g. the last row)?

I am uploading the modified version. Also a quick note, with vba you have to declare each variable separately. For example

Please Login or Register  to view this content.

If you put

Please Login or Register  to view this content.

Excel will assign x as a variant and y as an integer.

abousetta

I had no idea about the x,y variant/integer... so thank you for that.. And thank you for the clean up..

I realize now it isn't deleting the previous data on rn so the unmodified lines are just kindof staying there.

**abousetta** · 07-11-2012, 01:11 PM

Is it working now? It's not clear from your post.

**BuzzOffSweetheart** · 07-11-2012, 01:32 PM

Originally Posted by abousetta

I have gone through your code and attempted to streamline where I can and see what the problem. The only thing I see that caught my eye is that nPreviousRow is not defined anywhere even though it used several times. What should this be (e.g. the last row)?

I am uploading the modified version. Also a quick note, with vba you have to declare each variable separately. For example

Please Login or Register  to view this content.

If you put

Please Login or Register  to view this content.

Excel will assign x as a variant and y as an integer.

abousetta

Thank you very much for your help abousetta.. But it didn't fix the problem..

I got help this morning from a colleague and fixed it by adding to the macro to go through and delete lines originally there..

Please Login or Register  to view this content.

]

Complicated Macro doesn't work properly if lines are deleted

LinkBack

Thread Tools

Rate This Thread

Display

Complicated Macro doesn't work properly if lines are deleted

Re: Complicated Macro doesn't work properly if lines are deleted

Re: Complicated Macro doesn't work properly if lines are deleted

Re: Complicated Macro doesn't work properly if lines are deleted

Re: Complicated Macro doesn't work properly if lines are deleted

Re: Complicated Macro doesn't work properly if lines are deleted

Re: Complicated Macro doesn't work properly if lines are deleted

Re: Complicated Macro doesn't work properly if lines are deleted

Re: Complicated Macro doesn't work properly if lines are deleted

Re: Complicated Macro doesn't work properly if lines are deleted

Re: Complicated Macro doesn't work properly if lines are deleted

Re: Complicated Macro doesn't work properly if lines are deleted

Re: Complicated Macro doesn't work properly if lines are deleted

Re: Complicated Macro doesn't work properly if lines are deleted

Re: Complicated Macro doesn't work properly if lines are deleted

Re: Complicated Macro doesn't work properly if lines are deleted

Re: Complicated Macro doesn't work properly if lines are deleted

Re: Complicated Macro doesn't work properly if lines are deleted

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions