Adding A Blank Row To A Table In Error

**TheScott** · 05-26-2015, 10:46 AM

I posted this problem a couple of days ago but received no reply.
Here

Recreated it in a worksheet example rather than a snippet of code.

ExtraTableRowExp.xlsm

The problem is outlined in the worksheet but essentially I have a userform that enters data into Table1 only… except if a particular textbox contains a value(If Textbox.value > 0 ) then it is supposed to ALSO enter some of the data into Table2. Problem is that a new row >>blank<< row is added to Table2 when the target textbox is left at 0. It works as it should if a value greater than 0 is entered..

Any assistance helping me understand what I have done wrong would be appriciated

Regards

Scott

**Pauleyb** · 05-26-2015, 03:48 PM

This code is adding a line to the second table every time the enter transaction button is clicked.

Please Login or Register  to view this content.

It is in your CommandButton1_Click subroutine.
BTW - if you don't know how to trace through your code, line by line and by using break points, you should learn. It helps in this type of issue.

**TheScott** · 05-27-2015, 03:33 AM

Thank You for your reply. This was another good learning moment. I had used debug, or at least my limited understanding of its capabilities, to no avail. Knowing the offending code lead me to apply a break point their so I could see it add the line. My question is, if you don’t mind, how do you apply a systematic approach to using debug that leads you to this line?

I have stripped out many functions and lines of code to provide this example…(My coding is at best, my attempt to learn VBA and at worst a mishmash of code that I have gotten from the internet that I have slim knowledge of how it functions. Neither of which represents a clear style or approach.) The original would have been more difficult to weed through so a little insight into how you approach the problem solving process would be of great benefit.

The solution to this problem…. Or at least what I did that worked, was to move that line of code to the If statement which adds data to Table 2. Is that a proper fix or should I have declared something different at the beginning?

Thank You again…. One day, I might be able to answer questions…. But not for the foreseeable future.

Best Regards
Scott

**Pauleyb** · 05-27-2015, 10:25 AM

I found this error by starting at the code for the command button click. I knew it had to start there. I then looked for actions to you second table, and since this line was one of the first lines in the code, I found it fairly quickly. I used a breakpoint just ahead of it and then stepped through the code (using F8 key) to confirm my suspicions. So, for me, this was fairly simple, and I thank you for stripping extraneous code as I'm sure that helped. But, debugging is a bit of an art and the good news is that as you program and debug more and more, it gets acquired.

Generally, I have a sense of where to start. In this case it was with the command button click. If the code is very large or complex, I scatter breakpoints at likely suspects or after logical code blocks (e.g. for loops, if...then...else blocks, etc.). So, I run the code, which then hits a breakpoint. Check to see if my error occurred. If it has, then I have reduced the area of the code and I stop the code, set up more breakpoints, and run it again. If it has not, then I hit F5 to run to the next breakpoint. So, the process is a little iterative and it keeps getting reduced to a smaller and smaller block of code. At some point, the block of code is small enough that I step through the code using F8. While stepping through, I hover over variables or put them on the watch list to see what values they are and how they are being changed. Eventually, I hopefully find my issue.

In some cases this is easier said than done, especially when there are a bunch of calculations that are dependent on each other and you don't know you have it wrong until the very end. Think of a compression algorithm - you write the code to compress and then to decompress, but after decompression the files don't match. Where to start? Well, then I would start with very simple input files. So, as I was saying, debugging is a bit of an art with a few tools to help you out.

As for your solution, that seems like a great solution. No need to add the line and set the variable until you determine it is needed.

**TheScott** · 05-27-2015, 02:42 PM

Thank you for your thought process. Coming from the hardware world the same thought process of finding likely segments and subdividing processes is similar. I just have to learn to clearly separate various code processes, which isn’t easy given that some of the code works ‘cause someone else shared it… all part of the learning curve… and like you say, troubleshooting is as much an art as it is anything. Anyway, you have been of real assistance to me in my personal journey to further my knowledge of VBA and programing in general… 65 is a difficult starting point but, you know what they say about unused grey matter….

Thanks heaps… have a good remainder of your day

Scott