For Each... loop Question

[travm]

Im trying to make some code to go through a list of numbers, and pick the next highest number from the one entered in a form. Then I need to do some processing with that number (i need to create a worksheet with that number as the name, and place that number in a few cells on that worksheet, and the main worksheet, but thats all stuff i think i can do).
This is what I have made so far from reading about on the web.
I have tried about 30 different suggestions from similar pieces of code I have found, nothing has worked, not even close.

Please Login or Register  to view this content.

This does nothing, at all. In userform2 i have 2 text boxes and one command button. I'm not really new to VBA just not very experienced.

[Tarball]

Are you sure you are even entering the For Loop?

I think this line is suspect:

Please Login or Register  to view this content.

Change it to read:

Please Login or Register  to view this content.

[travm]

that did seem to change something, however now i get an output into textbox2 that seems to come from another worksheet within this workbook, or perhaps a cell somewhere in this worksheet. I would expect it to output the value of the cell which is greater than the value in textbox1, but no, i get some text from somewhere, not even sure where the text is coming from. (the workbook is huge so looking for the text "ser 11" could take all day, and could also be in 100 different places),
ok i found the ser 11, odly enough it is a block of text in range (A1:A1296), however clearly the loop did not stop when it passed the first value larger than the value in textbox1.

[Tarball]

The thing to watch out for now is that you are referencing the proper sheet. As for finding the value, set a breakpoint at the top of the loop and step into the function (F8). You can also add code to highlight the cell that the data is being pulled from. For example something like this (didnt test this so I hope the syntax is correct). this will set the cell fill color to red for the active cell.

Please Login or Register  to view this content.

[travm]

I get a type mismatch error on my workbook name,
Im not sure how to properly call that one.

[travm]

Please Login or Register  to view this content.

I have come up with this, but now i get a subscript out of range error on the line Set wb....

[Tarball]

Sorry - you must set it like the following (as an example). wb will be set to the name of the active workbook when this code executes. Your code above should work just fine. What line of code is throwing the error?

Check to make sure that you have spelled the names correctly and that you do not have any added spaces or anything else to cause the name of the workbook or worksheet to be different.

Please Login or Register  to view this content.

[travm]

something new again, this time it just picked a number, it was looking for next number higher than 70000, but instead it went to 70691A (which is in the range, but clearly not the nearest value to 70000) It also didnt appear to turn anything red.

[travm]

something new again, this time it just picked a number, it was looking for next number higher than 70000, but instead it went to 70691A (which is in the range, but clearly not the nearest value to 70000) It also didnt appear to turn anything red.

Please Login or Register  to view this content.

That is my code atm

[Tarball]

Hey but look at the bright side - you are making progress. I bet you turned something red! Need to find it. Make sure you are stepping through the logic and not just executing it then looking for results. Its better to step through slowly and see what code is running in addition to what the outcome is. You can also use your cursor over variables to see their values. Finally, use Debug.Print statements to output values in the Immediate window.

For example

Please Login or Register  to view this content.

To get around the issue you just described you can use:

Please Login or Register  to view this content.

[travm]

Adding that causes the code to appear to do nothing. textbox2 remains empty and the form doesnt seem to do anything.

[Tarball]

Ok that says that there are no numeric values that the loop is reading. (check your data to be sure).

Do you have to do something everytime a value is greater than what is in the textbox or just for the next greatest as you mentioned in your post

pick the next highest number from the one entered in a form.

If only for the "next greatest" then iterate through your data first, find the next highest then operate on that value.

[travm]

what could be causing the problem is that each of the values in that range are hyperlinks. The value in the cell afaik is simply the number, but then that cell is a hyperlink to another worksheet within this workbook.
I only need to perform operations with this "next greatest" number.
More specifically, the number I input is going to be used to create a new worksheet titled with that number, then it is going to be moved within the workbook (with thousands of worksheets) to before the next greatest worksheet, the easiest way to find the next greatest worksheet is to search the front page for the next greatest number in that range of A7:A1296.
It would be a huge timesaver to input new data this way as there are many standard responses, not to mention not having to manually create a new worksheet for each entry.
Not sure what you mean by iterate my data first.

[Tarball]

By iterate first, I meant find the next greatest value before attempting any further functionality. How do you know you have the "next greatest" number - Is the data already sorted that way?

Also, if the cell contents are simply a numerical value like 1000 (but also hyperlinked) then you can convert it using a cast operation like CINT(1000) or CLNG(1000) which will change the original datatype to the requested type (integer or long as noted above). If you have values greater than roughly 32,735 then use long or double.

[travm]

the data is already sorted numerically,
the way you enter data now is to scroll down until you find the next larger number than you want to enter, you insert a row, type in some info, then create a new worksheet, copy the template, paste the template, enter some information, rename worksheet to number, move worksheet to just before next largest number and save. Lots of work for only entering about 5 or 6 peices of information.

Also Im not really sure how to use the Cint function. It gives me an Error expected identifier

[travm]

got it to work, through recording some macros and figuring out how excel might actually locate the value in the cell instead of the full formula, its interesting the way it handles these hyperlinks. Even though the "Actual" data in the cell is just the number, since the cell is a hyperlink it seems to screw stuff up behind the scene. I got around this by noticing when i mouse-over the linked cell it says click to follow hyperlink, and click-hold to select the cell. I recorded a macro while doing the click-hold and found this put code out like this

Please Login or Register  to view this content.

I adjusted my code to this

Please Login or Register  to view this content.

and it works.
Tarball thanks for all your help, at least it kept me moving against the problem.

oh, however, the cells still dont turn red. but i do get my next highest number.

[Tarball]

Cool Man. Glad to see you knocked it out. There is no better feeling when programming to know that you took the problem the last mile and figured it out. I love that feeling.