Stuck with a VBA problem in Excel 2000

  Simsy 01:22 22 Jan 2003
  Simsy 01:22 22 Jan 2003

Hi chums,

I'm making some, slow, progress with a project in Excel,(2000), that needs some use of VBA...

Briefly, the workbook has 2 worksheets. The first sheet is essentially a "timesheet". For each date that appears on this there is a checkbox. The second sheet is an expenses claim form.

If the checkbox for any given day is ticked the idea is that the values contained in 3 cells of data from that day, (date, start time, finish time), are transferred to the claim form.

Because of the possibility that any number of these checkboxes may be ticked I need to incorporate into the VBA coding some sort of "next line" instruction....

For example lets assume that no data has yet been transferred, the first transfer of data will go to cells A1,B1,C1 on the 2nd sheet. The next transfer of data will, therefore, go to A2,B2,C2. Because there is no way of knowing which date will result in a second transfer of data I can't tie particular cells from the first sheet to the second.

Can anyone help with the syntax I need for this specific task? I've run out of obvious permutations! Once I have this sorted I can move onto the next part of it!!

(The example is much simplified, compared to the real thing, for the sake of clarity, though I can expand on this if necessary.)

Thanks in anticipation,



  VoG™ 08:29 22 Jan 2003


I'm not sure quite what the problem is. If you want to determine a row nimber based on which boxes are checked then you could use something along the lines of (in pseudo code)

If Box1 = True And Box2 = True And Box3 = True Then

iRow = 1
ElseIf Box1 = True And Box2 = True Then

iRow = 2

ElseIf Box1 = True And Box3 = True Then

iRow = 3

ElseIf Box2 = True And Box3 = True Then

iRow = 4

ElseIf Box1 = True Then

iRow = 5

ElseIf Box2 = True Then

iRow = 6

ElseIf Box3 = True Then

iRow = 7


iRow = 8

End If

If you want to find the first empty row in a sheet you can use something like

LastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row

which returns the last row in Column A that contains data.

Hope this helps.

  Hotfingers 11:32 22 Jan 2003

If the above post has not solved your problem could you expand on this as I am slightly confused.

  Simsy 14:01 22 Jan 2003

is transfer data to the NEXT AVAILABLE free cell, in ColA, on the second sheet.

The rows on the second sheet don't correspond to the rows on the first sheet.

The data on the first sheet, (date and times), may or may not need to be tranferred to the second sheet, hence the checkbox. Ticking the checkbox copies the data to the next available line on sheet 2.

It might be that the checkbox in row 10 on sheet 1 is the first that is ticked, so it needs to copy data to row 1 on sheet 2.... however it might be the 5th to be ticked, so it needs to copy the data to row 5 on sheet 2.

Does that make the problem clearer?

I'm certain that in the past I have seen reference to a "NextLine" function. That's what I'm trying to get at. I think your last point, Vog, is somewhere near the mark. It's not just a free Row I need however, as further along the Row, in cols F onward, there are already entries in the cells. These will result in calculations based on the entries made automatically,(what I'm trying to achieve)

Thanks for your time.... I know when this particular part is over there will be other bits to confound me!



  VoG™ 14:32 22 Jan 2003

You can write your own NextLine function and store the value in a worksheet cell ready for use the next time you open the workbook.

However a better approach is to find the last used row. This example assumes that there are 26 columns - adapt to suit:

LastRow = 0

For iCol = 1 To 26

xRow = Cells(Cells.Rows.Count, iCol).End(xlUp).Row

If xRow > LastRow Then LastRow = xRow

Next iCol

  Simsy 23:02 29 Jan 2003

that I'm not ignoring your advice... just haven't had a chance to get into it for a while.. and may not do so for another couple of weeks.

Presures of domesticity!



  VoG™ 23:05 29 Jan 2003

No probs!

  Simsy 10:52 06 Mar 2003

I have now solved this particular problem in the project, with help from another site, (one that I believe I first heard of from you, VoG),

The solution, with explanation as given, (which helps me to understand what you had proposed), was;

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.

Thanks again for all input,



  Simsy 10:55 06 Mar 2003

Site formatting makes that look a bit messy....

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

is the answer, the rest is the explanation!



This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

See mcbess's iconic style animated for Mercedes-Benz

iPhone X news: Release date, price, new features & specs

Black Friday 2017 : date, sites participants & bonnes affaires