Hangs Up with Target and If/Then

Flapjack

Board Regular
Joined
Aug 24, 2005
Messages
57
Office Version
  1. 365
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
What do you mean by 'hangs up'?
 

Flapjack

Board Regular
Joined
Aug 24, 2005
Messages
57
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,799
Messages
5,574,379
Members
412,589
Latest member
ArtBOM
Top