Do I need a Loop?

Posted by Dani on September 19, 2001 4:47 PM

Hi, this time is with a loop.
I have an UserForm which get information from the user and, Once the user clicks on the "Next" button, all the info goes into the cells.
Now, how can I create a loop in order to get the information from the UserForm into the cells just when that "Next" is clicked without overwriting the last values, this is, putting those valus in the next row?
It's is really clear I'm a newbie, right?

Thanks a lot.

Dani

Posted by Rob Jackson on September 19, 2001 11:38 PM

Morning Dani,
There are a couple of options. The first is to connect to a SpinButton / Scrollbar instead of your next button,and use that to generate your line number. the second is to keep a variable that tracks which row your are in or need. Link that with code in the following structure...

Range("'[workbookname]sheetname'!A" & rownum).value = TextBox.value

As rownum changes the info is stored in the A col of that row. You can increment the rownum on the NEXT button click event with...

RowNum = RowNum+1

Then all you need is to find where the end of the data is. there is various ways of doing this but I usually use something like this.

For RowNum=1 to 65536
IF Range("'[workbookname]sheetname'!A" & rownum).value = "" then
Exit for
end if
next RowNum

This will scan down col A until it finds a blank cell and this will be the value of RowNum. Use this in your initialisation procedure. Beware, this only works if the data in that column is always filled in otherwise it could stop halfway down your data. I tend to use this method because I am checking other things during my initialisation.

Good Luck...

Posted by Juan Pablo on September 20, 2001 6:06 AM

A quicker way to find the first unused row of data is this:

FinalRow = Range("A65536").End(xlUp).Offset(1,0).Row

That way you get the row number of the first available row, and can start entering data from there.

If your data isn't in Column A, change the Range to adjust to your needs.

Juan Pablo

Posted by Dani on September 20, 2001 3:18 PM

Thanks to Rob Jackson and Juampi (gracias)

Thanks guys, I solved it in another way yesterdey because my boss ws waiting for it, but THANKS A LOT ANYWAY.

dani