Stuck with a VBA problem in Excel 2000

  Simsy 01:22 22 Jan 03
Locked
  Simsy 01:22 22 Jan 03

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,

regards,

Simsy

  VoG™ 08:29 22 Jan 03

Hi,

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

Else

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 03

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

  Simsy 14:01 22 Jan 03

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!

Regards,

Simsy

  VoG™ 14:32 22 Jan 03

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 03

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!

Regards,

Simsy

  VoG™ 23:05 29 Jan 03

No probs!

  Simsy 10:52 06 Mar 03

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), MrExcel.com

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,

Regards,

Simsy

  Simsy 10:55 06 Mar 03

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

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

is the answer, the rest is the explanation!

Regards,

Simsy

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

Surface Pro 5 News - release date, UK price, features, specs

Gear VR vs. Daydream: Which delivers the best VR experience?

Best Mac antivirus 2017