MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How do I check for the end of a row in Cell O and jump to the next row to Insert a New Record?


Posted by Kurt on April 07, 2001 11:39 AM

Hello all,

How can I get vba to search for the end of a record with the last data entered in Column O and then have the cell selector jump to the next row to enter a new record starting in cell "A"

Again, any help would be greatly appreciated. I am sure this is an easy one for Tim or Celia.

Thanks,

Kurt


Posted by Dave Hawley on April 07, 2001 2:11 PM


Hi Kurt

Is this what you mean ? Note I have used the sheets CodeName as apposed to the Tab name.

Sub TryThis()
Dim LastCell As Long
LastCell = Sheet1.Range("O65536").End(xlUp).Offset(1, 0)
Sheet1.Range.Range("A" & LastCell) = "NewRecord"
End Sub


Dave

OzGrid Business Applications

Posted by Kurt on April 07, 2001 2:58 PM

Thanks for the fast response Dave, but New Record does not work in Excel 97

Hello Dave,

Thanks for your very fast response on a Saturday. I am glad to know some of us are still out there coding!! he he

I am using Excel 97 if that makes any different the code stops on the line of the New Record.

Again thanks for your prompt attention and happy Passover!!

Kurt

Posted by Dave Hawley on April 07, 2001 3:10 PM

Re: Thanks for the fast response Dave, but New Record does not work in Excel 97


Hi Kurt

It is 6 am here in Oz, hence my code typo :o)

Should be this:

Sub TryThis()
Dim LastCell As Long
LastCell = Sheet1.Range("O65536").End(xlUp).Offset(1, 0).Row
Sheet1.Range("A" & LastCell) = "NewRecord"
End Sub

Forgot to put .Row

Dave


OzGrid Business Applications

Posted by Kurt on April 07, 2001 3:33 PM

close and almost there!!

Hello Dave,

I wish I could pour you a cup of coffee or something!!

Yes, the Row helps, but I am trying to keep what is in cell A and have the cell pointer move down to the next blank line in the database.

Thanks again,

Kurt

Posted by Dave Hawley on April 07, 2001 3:56 PM

Re: close and almost there!!


It's ok Kurt, I now have a coffee!

Sub TryThis()
Dim LastCell As Long
LastCell = Range("O65536").End(xlUp).Offset(1, 0).Row
Range("A" & LastCell).Select
End Sub

This will select the same row in column A as the last row+1 in Column O

Dave

OzGrid Business Applications

Posted by Kurt on April 07, 2001 4:26 PM

Re: close and almost there!!

Hello Dave,

Would you please take a look at the email I sent you. Again thanks for your patience and help.

Kurt

Posted by Dave Hawley on April 07, 2001 6:59 PM

Re: close and almost there!!

Kurt, I'm not at that address today, so could you send it to dhawley@micl.com.au


Dave
OzGrid Business Applications

Posted by Kurt on April 07, 2001 8:22 PM

Re: close and almost there!!

Hello Dave,

I tried to send it there but my aol account says it doesn't exist so here is the total code. It may look ugly since I am experimenting.

Private Sub CommandButton1_Click()
' Select cell b3, *first line of data*.
Sheets("Patient Info").Select
Range("B3").Select

' This code copies the patient data
Sheets("Patient Info").Select
Range("B3:B17").Select
Selection.Copy
Sheets("Patient Names").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False

''Sub TryThis()
''Dim LastCell As Long
''Dim i As Long
''LastCell = Range("O65536").End(xlUp).Offset(1, 0).Row
''Range("A" & LastCell).Select

Dim LastCell As Long
LastCell = Sheet1.Range("O65536").End(xlUp).Offset(1, 0).Row
Sheet1.Range("A" & LastCell) = "NewRecord"


''Range("A" & LastCell).Offset(0, 1).Select
''For i = Selection.Rows.Count To 1 Step 1

''If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
'' ActiveCell.Select
''Selection.Rows(i).EntireRow.Delete
End If
Next i

''Range("A").Offset(1, 0).Select
''LastCell = Range("O65536").End(xlUp).Offset(1, 0).Row
''Offset(1, 0)("A" & LastCell).Select
''ActiveCell.Offset(1, 0).Row
''End Sub


Range("A1").Select
Sheets("Patient Info").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B3").Select
frmPatientName.Hide

End Sub

Thanks,

Kurt

Posted by Dave Hawley on April 07, 2001 10:53 PM

Re: close and almost there!!

''Sub TryThis() ''Dim LastCell As Long ''Dim i As Long ''LastCell = Range("O65536").End(xlUp).Offset(1, 0).Row ''Range("A" & LastCell).Select Dim LastCell As Long LastCell = Sheet1.Range("O65536").End(xlUp).Offset(1, 0).Row Sheet1.Range("A" & LastCell) = "NewRecord" ''Range("A" & LastCell).Offset(0, 1).Select ''For i = Selection.Rows.Count To 1 Step 1 ''If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then '' ActiveCell.Select ''Selection.Rows(i).EntireRow.Delete End If Next i

Kut, you could shorten the first bit to:
'This code copies the patient data
Sheets("Patient Info").Range("B3:B17").Copy
Sheets("Patient Names").Range("A2").PasteSpecial Transpose:=True
Application.CutCopyMode = False

The rest though you will have to tell me what it is you want to do as i cannot tell from looking at it. E.G; you have:

LastCell = Sheet1.Range("O65536").End(xlUp).Offset(1, 0).Row
Sheet1.Range("A" & LastCell) = "NewRecord"


Range("A" & LastCell).Offset(0, 1).Select
For i = Selection.Rows.Count To 1 Step 1

If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
ActiveCell.Select
Selection.Rows(i).EntireRow.Delete
End If
Next i

Which is trying to loop through 1 cell in Column B


Dave

OzGrid Business Applications

Posted by Kurt on April 08, 2001 7:48 AM

Re: close and almost there!!

Hello Dave,

Thanks for your patience!! What I am trying to do is to take values that are entered on a sheet called Patient Info in cells B3 to B17 and then copy and paste them onto another worksheet called Patient Names.

I would like the cell pointer to recognize when there is a new blank row and paste the next name copied form Patient Info to Patient Names every time.

I hope this helps clarify things a bit :-)

Thanks,

Kurt