VBA help please, for 'Next available Row' SOLVED

[simsy]

Hi folks, thanks in anticipation for your assistance?.

I am working on a timesheet/expensesform combination in Excel 2000
The objective is as follows:

There are 2 worksheets in the book. The first is a timesheet for tracking the hours I work. This part of it I think I have covered. Depending on the start and finish times of the shift on the day in question I may be able to claim mileage for using my own car, (e.g. if I start at 04:00 in the morning there is no public transport available, so I need to use my own car and can claim a little for fuel). The second worksheet is the actual claimform for claiming the mileage. I have this covered such that all I need enter on this form, if I want to claim for a given day, is the Date and Start/Finish times on that day.

I want to automate things such that checking a box on the timesheet transfers the Date and Start/Finish times to the claim form. I cannot change the layout of the form, as this is a standard form prescribed by my employer. The completed form, (which is printed, signed by me, then submitted), should not have ?empty? lines. It is, for example, possible that I work 6 consecutive days with only the first and last of these days giving rise to a claim. The claim form should have the data entered on 2 consecutive Rows, not with 4 blank rows between them. (Other constraints, which I needn?t detail here, mean that a ?Sort? before printing isn?t an option)

The timesheet covers a 4 week period. Columns D, F and G carry the date, start time, and finish time. (Note Col E doesn?t enter into this). Further along, in Col ?M?, there is a Checkbox. (The checkbox is named according to the Row it is in, e.g. the Checkbox associated with Row 13 is named C13). I have made sufficient progress to make sure the Checkbox(es), are enabled/disabled, (and also some other VBA aspects of the project), according to the correct time parameters. What I need help with is finding the NEXT AVAILABLE ROW on the claimform so that the data can be pasted in the correct place.

The Date, Start and Finish times on the claim form are Columns A, B and D, (Note ?C? not used). Can someone please help me with VBA code for this? It?s just locating the ?Next Free Row? that I need help with.

One of the main reasons for undertaking this exercise is to learn a bit more about VBA programming, so any explanation of any solution would be most welcome!

I hope the problem is clear?

Thanks in anticipation,

(This is the first time I've posted a problem here so apologies if I've erred with any protocols!)

Regards

Simsy

 

[Joe4]

I think maybe you supplied too much information, and it might have scared some people off!

If you simply want to select the next available row, the following VBA formula should work:

Range("A65536").End(xlUp).Offset(1, 0).Select

The key is to pick a column that will always have data (I chose column A). Row 65536 is the last possible row a spreadsheet can have. The End(xlUp) command goes up until it finds a cell with data. The Offset(1,0) goes to the row just below that one.

 

[simsy]

Thanks.....

for the swift response, and the explanation, which I think I understand. I'll be able to try it later, when I get home.

However, if I understand your explanation correctly, this will get me to the FIRST cell in row A that has data, then go to the one below that?

I need to go to the LAST row in row A with data, then go to the one below it.

Have I misunderstood what you've achieved. Unfortunately I am not in a position to try it out right at the moment.

Thanks,

Regards,

Simsy

 

[Joe4]

No, you misunderstood.

Range("A65536").End(xlUp).Offset(1, 0).Select

"Range("65536")" selects the LAST possible cell (as defined by Excel) in column A.
"End(xlUp)" then works it ways up from the last cell and stops when it finds a cell with data.
"Offset(1,0)" then selects the row just below that row with data

Essentially you're starting from the last row and working your way up until you find data. Try it, you'll see!

 

[simsy]

Thank you.....

I've just got home and received this information. I hope to try it later this evening. The way you have now explained it I can now make sense of!

I had assumed that in the bit of code;
"End(xlUp)"
the "Up" meant "Update".... now you've made it clear it means "Upwards" it all makes sense. In fact re-reading your first answer I can now see that's what you said! The confusing thing is that "Upwards" on a worksheet is actually "Downwards" numerically!

I presume that (xlDown) would do the opposite?

Anyway, many thanks, I'm sure that with this info I can crack the rest of the project. I will, (I hope!), be able to get into it in the next couple of days, when I've finished installing my sisters new kitchen!

Regards,

Simsy

 

[Joe4]

Yes, you are correct in that "xlDown" will find the next row of data below the cell you are starting from. This is useful when you have a bunch of blank rows and want to find out where the next section of data starts.

 

[simsy]

Again, thank you so much....

I have just applied what you've toaught me in a test workbook, obviously, as you knew, sucessfully!

Now that I understand it I will now be able to apply it in the real project... that will take a little longer.

Is there a way I can now mark this thread as solved/resolved?

Regards,

Simsy

 

[Joe4]

I think some people edit their original message and put "SOLVED" after the title (I've never tried it myself, though).