How to get variables in first open cell

[Mark43]

I'm trying to do is get three variables (name, score, grade) in columns A, B, and C respectively. I dimmed name as string, score as double, grade as string, and i as long. The first part takes input for name and score and determines grade based on numerical score.

I think this code snippet caused Excel to crash. If so then why?

Please Login or Register  to view this content.

[TMS]

Probably not crashing ... just gently looping through over 3 million cells

Rows.Count is the number of rows in the worksheet and is over 1 million.

You could do this ...

Please Login or Register  to view this content.

But it's still too many rows. You need to determine how many rows of data you have.

[mgs73]

Rows.Count is the number of rows in the sheet, i.e. 1,048,576! You do this three times!

Something like this should get you closer to what you want, but without seeing the spreadsheet I'm really only guessing:

Please Login or Register  to view this content.

[Mark43]

Dang... I thought there were fewer than 100K rows.

My worksheet is empty. Why wouldn't it find and stop at the first open cell in each column (i.e. A1, A2, and A3)?

[TMS]

Your testing the cell and doing/not doing something with it. You don't test for blank and exit.

[Mark43]

Your testing the cell and doing/not doing something with it. You don't test for blank and exit.

I believe you... I just don't understand where my attempt failed. Does this line not test for the blank and then _if blank_ go ahead and do something (specifically, assign a value to that cell)?

Please Login or Register  to view this content.

[mgs73]

Ok, so your worksheet is empty...? What do you want it to look like after the macro is run?

Try this:

Please Login or Register  to view this content.

[Mark43]

Ok, so your worksheet is empty...? What do you want it to look like after the macro is run?

Try this:

Please Login or Register  to view this content.

Nice. Can you explain the syntax and how it works?

What I want is for the three cells to be populated once an open cell is found.

I came up with this:

Please Login or Register  to view this content.

I kept it to the first 10 rows after you and TMS pointed out a million rows, etc. The problem with
mine is the limitation, though, since the spreadsheet may have occupied cells there. I added the
last MsgBox in that case (and it was instructive as it occurs when i = 11, not 10).