Hangs Up with Target and If/Then

[Flapjack]

I've been bungling my way through my first attempt at some programming and ran into a snag. Basically, the user enters a quantity and part number. If the part number entered matches the target number, then a dialog box is shown and, depending on the option they choose, two corresponding quantities and part numbers will be shown on the next two available rows. What I have written here are two checks: first, to make sure that the row numbers written to are not in a header area which has some information (part of it is a graphic picture) that I don't want written over (rows 36 - 49). And second, to check if the cell it wants to write to doesn't already have something entered in it. If it does, then it increases the row number by one and goes back and re-does the checking.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
<SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#007F00">'   Determine Row</SPAN>
<SPAN style="color:#007F00">'   RowNumber is Row for Screws, RowNumber2 is Row for T-Nuts</SPAN>
    RowNumber = Target(2, 0).Row
    RowNumber2 = RowNumber + 1
<SPAN style="color:#007F00">'   Check if Row is in Header Area</SPAN>
Header:
    <SPAN style="color:#00007F">If</SPAN> RowNumber = 36 <SPAN style="color:#00007F">Then</SPAN> RowNumber = 50
    <SPAN style="color:#00007F">If</SPAN> RowNumber = 50 <SPAN style="color:#00007F">Then</SPAN> RowNumber2 = 51
    <SPAN style="color:#00007F">If</SPAN> RowNumber = 35 <SPAN style="color:#00007F">Then</SPAN> RowNumber2 = 50
<SPAN style="color:#007F00">'   Check if Cell is Empty</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Cells(RowNumber, 3) <> "" <SPAN style="color:#00007F">Then</SPAN>
        RowNumber = RowNumber + 1
        RowNumber2 = RowNumber2 + 1
        <SPAN style="color:#00007F">GoTo</SPAN> Header
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Cells(RowNumber2, 3) <> "" <SPAN style="color:#00007F">Then</SPAN>
        RowNumber2 = RowNumber2 + 1
        <SPAN style="color:#00007F">GoTo</SPAN> Header
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#007F00">'   Determine Quantity</SPAN>
    PartQuantity = Target(1, 0).Value
<SPAN style="color:#00007F">If</SPAN> Target = "3842518738" <SPAN style="color:#00007F">Then</SPAN> UserForm1.Show
<SPAN style="color:#00007F">If</SPAN> Target = "3842525480" <SPAN style="color:#00007F">Then</SPAN> UserForm1.Show
<SPAN style="color:#00007F">If</SPAN> Target = "3842525766" <SPAN style="color:#00007F">Then</SPAN> UserForm1.Show
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

This actually works fine except if I have already entered information in row 34 or 35 and then go back and re-enter information in those rows. Then it hangs up in the "Check if Cell is Empty" section: either at the second End If, or the second RowNumber2 = RowNumber2 + 1 statement.

Am I making sense? Any ideas?

Thanks!

 

[Flapjack]

Anyone?

 

[Norie]

What do you mean by 'hangs up'?

 

[Flapjack]

It locks up and I can't do anything until I press Escape. When I press the escape button it brings up the Debug box and shows it stopped at one of the two statements I noted above.

 

[Norie]

Sounds to me as though you are getting into a continuous loop.

You really should try to avoid using Goto, for one thing it makes it hard to understand the structure/flow of the code.

Because of that I can't really make a concrete suggestion for a solution.

I'm pretty sure there must be another method to check if the target is/isn't in the header area. Perhaps you could use the Intersect method?