Selecting cells off a button

[Sir Phoenix]

Need code to do the following:

Pseudocode:

cmdPrev()

if there is a selected cell
if selected cell is not the first record
select cell that is one row higher
else
select last record
end if
else
select last record
end if

end sub

cmdNext()

if there is a selected cell
if selected cell is not the last record
select cell that is one row below
else
select first record
end if
else
select first record
end if

end sub

The first record will always be cell A2. The last record will be dynamically updated, but will be the last populated cell in column A.

[Sir Phoenix]

Actually, since the methods are so similar... if I can get the code for one, I can practice figuring out the other. :)

[Sir Phoenix]

So far, came up with this, but it isn't working.


Private Sub cmdNext_Click()
Dim rngNext As Range
With ThisWorkbook.Sheets(5)
If Selection Is Nothing Then
.Range("A2").Select
Else
Set rngNext = Selection
If rngNext = .Range(.Cells(Rows.Count, "A").End(xlUp)) Then
.Range("A2").Select
Else
Selection.Offset(-1, 0).Select
End If
End If
cmbEmpName.Value = Selection.Value
End With
End Sub

[Sir Phoenix]

On second thought, all I need is the syntax on how to compare one range containing cell to another range/cell and see if they are the same.

Also the syntax on referencing the value of a given cell if it's not range.value.

[Jacob Hilderbrand]

If you want to compare values you can use this:


If Range("A1").Value = Range("B1").Value Then

Or


If ActiveCell.Value = Range("B1").Value Then


Or


If ActiveCell.Value = ActiveCell.Offset(0,1).Value Then

[Sir Phoenix]

THANK YOU!!!

[Sir Phoenix]

Almost got it. My new code looks like this.


Private Sub cmdNext_Click()
With ThisWorkbook.Sheets(5)
If Selection Is Nothing Then
.Range("A2").Select
Else
If ActiveCell.Value = .Range(.Cells(Rows.Count, "A").End(xlUp)).Value Then
.Range("A2").Select
Else
Selection.Offset(-1, 0).Select
End If
End If
cmbEmpName.Value = ActiveCell.Value
End With
End Sub

However, the if statement where I compare the (if activecell.value = .range(.cell.blah)) is giving me an error message of "Run-time error '1004': Application-defined or object-defined error. What is this?

[gibbo1715]

i think this is something most have had a problem with at one time or another ( I did and got a lot of help here)

so I made a template to share, hope it helps

Anyone wants to improve on this ( As im sure you will)

feel free

gibbo

[royUK]

To get the first data entry - allowing for the first row to be a header row, and that column A is part ot the Data table.


Dim FirstCl As Range 'first data Entry
'allow for rows being added/deleted above header row
Set FirstCl = Range("a1").End(xlDown).Offset(1, 0)
To get the last data entry


Dim LastCl As Range
Set LastCl = Range("a65536").End(xlUp) 'last used cell in column A

Check out the DatabaseForm example here (http://www.excel-it.com/workbookdownloads/workbookdownloads.htm)

[Sir Phoenix]

I didn't check out the last file posted, but with the code in the last post, I learned something new, and my code now works! Thanks a bunch!