Hangs Up with Target and If/Then

Flapjack

Board Regular
Joined
Aug 24, 2005
Messages
59
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!
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Norie

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

Flapjack

Board Regular
Joined
Aug 24, 2005
Messages
59
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,351
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,117
Messages
5,857,477
Members
431,882
Latest member
saaaaaaaaaaaaaaaaaaaaaa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top