I have a program which reads data into columns C-H. The number of rows
varies from case to case.
I want the user to select a starting cell from the dat in column C
I have the following which seesms clumsy but works:

rw = ActiveCell.Row
cl = ActiveCell.Column
Range("c2").Select
lastrow = Range(Selection, Selection.End(xlDown)).Cells.Count + 1
If rw < 2 Or rw lastrow - 1 Then
MsgBox "Start cell outside data range"
Exit Sub
End If
If cl < 3 Then
MsgBox " Start cell in wrong column"
Exit Sub
End If

Before exiting the sub with an error, I would like to reset the active cell
to the (incorrect) cell the user selected.

Can someone tell me how to do this please? Or suggest a better way?
Thanks
Chris

Isn't the cell in error already selected?

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"inquirer" wrote in message
...
I have a program which reads data into columns C-H. The number of rows
varies from case to case.
I want the user to select a starting cell from the dat in column C
I have the following which seesms clumsy but works:

rw = ActiveCell.Row
cl = ActiveCell.Column
Range("c2").Select
lastrow = Range(Selection, Selection.End(xlDown)).Cells.Count + 1
If rw < 2 Or rw lastrow - 1 Then
MsgBox "Start cell outside data range"
Exit Sub
End If
If cl < 3 Then
MsgBox " Start cell in wrong column"
Exit Sub
End If

Before exiting the sub with an error, I would like to reset the active
cell
to the (incorrect) cell the user selected.

Can someone tell me how to do this please? Or suggest a better way?
Thanks
Chris

No, it gets superceded by Range("c2").Select

"Rob van Gelder" wrote in message
...
Isn't the cell in error already selected?

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"inquirer" wrote in message
...
I have a program which reads data into columns C-H. The number of rows
varies from case to case.
I want the user to select a starting cell from the dat in column C
I have the following which seesms clumsy but works:

rw = ActiveCell.Row
cl = ActiveCell.Column
Range("c2").Select
lastrow = Range(Selection, Selection.End(xlDown)).Cells.Count + 1
If rw < 2 Or rw lastrow - 1 Then
MsgBox "Start cell outside data range"
Exit Sub
End If
If cl < 3 Then
MsgBox " Start cell in wrong column"
Exit Sub
End If

Before exiting the sub with an error, I would like to reset the active
cell
to the (incorrect) cell the user selected.

Can someone tell me how to do this please? Or suggest a better way?
Thanks
Chris

"inquirer" ha scritto nel messaggio
...
I have a program which reads data into columns C-H. The number of rows
varies from case to case.
I want the user to select a starting cell from the dat in column C
I have the following which seesms clumsy but works:

rw = ActiveCell.Row
cl = ActiveCell.Column
Range("c2").Select
lastrow = Range(Selection, Selection.End(xlDown)).Cells.Count + 1
If rw < 2 Or rw lastrow - 1 Then
MsgBox "Start cell outside data range"
Exit Sub
End If
If cl < 3 Then
MsgBox " Start cell in wrong column"
Exit Sub
End If

Before exiting the sub with an error, I would like to reset the active
cell
to the (incorrect) cell the user selected.





rw = ActiveCell.Row
cl = ActiveCell.Column

lastrow = Range("C2").End(xlDown).Row

If rw < 2 Or rw lastrow Then
MsgBox "Start cell outside data range"
Exit Sub
End If

If cl < 3 Then
MsgBox " Start cell in wrong column"
Exit Sub
End If

So if you're wanting to select the cell again:

Cells(rw, cl).Select


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"inquirer" wrote in message
...
No, it gets superceded by Range("c2").Select

"Rob van Gelder" wrote in message
...
Isn't the cell in error already selected?

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"inquirer" wrote in message
...
I have a program which reads data into columns C-H. The number of rows
varies from case to case.
I want the user to select a starting cell from the dat in column C
I have the following which seesms clumsy but works:

rw = ActiveCell.Row
cl = ActiveCell.Column
Range("c2").Select
lastrow = Range(Selection, Selection.End(xlDown)).Cells.Count + 1
If rw < 2 Or rw lastrow - 1 Then
MsgBox "Start cell outside data range"
Exit Sub
End If
If cl < 3 Then
MsgBox " Start cell in wrong column"
Exit Sub
End If

Before exiting the sub with an error, I would like to reset the active
cell
to the (incorrect) cell the user selected.

Can someone tell me how to do this please? Or suggest a better way?
Thanks
Chris

Thank you, that does it

"Rob van Gelder" wrote in message
...
So if you're wanting to select the cell again:

Cells(rw, cl).Select


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"inquirer" wrote in message
...
No, it gets superceded by Range("c2").Select

"Rob van Gelder" wrote in
message
...
Isn't the cell in error already selected?

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"inquirer" wrote in message
...
I have a program which reads data into columns C-H. The number of
rows
varies from case to case.
I want the user to select a starting cell from the dat in column C
I have the following which seesms clumsy but works:

rw = ActiveCell.Row
cl = ActiveCell.Column
Range("c2").Select
lastrow = Range(Selection, Selection.End(xlDown)).Cells.Count + 1
If rw < 2 Or rw lastrow - 1 Then
MsgBox "Start cell outside data range"
Exit Sub
End If
If cl < 3 Then
MsgBox " Start cell in wrong column"
Exit Sub
End If

Before exiting the sub with an error, I would like to reset the
active
cell
to the (incorrect) cell the user selected.

Can someone tell me how to do this please? Or suggest a better way?
Thanks
Chris

Another couple of options.

You could drop the select:

Option Explicit
Sub testme2()

Dim rw As Long
Dim cl As Long
Dim lastRow As Long

With ActiveSheet
rw = ActiveCell.Row
cl = ActiveCell.Column
lastRow = .Range("c2").End(xlDown).Row + 1
If rw < 2 _
Or rw lastRow - 1 Then
MsgBox "Start cell outside data range"
Exit Sub
End If
If cl < 3 Then
MsgBox "Start cell in wrong column"
Exit Sub
End If

'more code here...

End With

End Sub


But maybe it would be simpler to just have your macro find the next cell that
you want:


Sub testme1()

With ActiveSheet
.Cells(.Rows.Count, "C").End(xlUp).Offset(1, 0).Activate

'more code here...

End With

End Sub

inquirer wrote:

I have a program which reads data into columns C-H. The number of rows
varies from case to case.
I want the user to select a starting cell from the dat in column C
I have the following which seesms clumsy but works:

rw = ActiveCell.Row
cl = ActiveCell.Column
Range("c2").Select
lastrow = Range(Selection, Selection.End(xlDown)).Cells.Count + 1
If rw < 2 Or rw lastrow - 1 Then
MsgBox "Start cell outside data range"
Exit Sub
End If
If cl < 3 Then
MsgBox " Start cell in wrong column"
Exit Sub
End If

Before exiting the sub with an error, I would like to reset the active cell
to the (incorrect) cell the user selected.

Can someone tell me how to do this please? Or suggest a better way?
Thanks
Chris

--

Dave Peterson

I meant to ask why you add 1 to the lastrow only to subtract it later. I
guessed that maybe you were using it in parts of the code that weren't posted.



Dave Peterson wrote:

Another couple of options.

You could drop the select:

Option Explicit
Sub testme2()

Dim rw As Long
Dim cl As Long
Dim lastRow As Long

With ActiveSheet
rw = ActiveCell.Row
cl = ActiveCell.Column
lastRow = .Range("c2").End(xlDown).Row + 1
If rw < 2 _
Or rw lastRow - 1 Then
MsgBox "Start cell outside data range"
Exit Sub
End If
If cl < 3 Then
MsgBox "Start cell in wrong column"
Exit Sub
End If

'more code here...

End With

End Sub

But maybe it would be simpler to just have your macro find the next cell that
you want:

Sub testme1()

With ActiveSheet
.Cells(.Rows.Count, "C").End(xlUp).Offset(1, 0).Activate

'more code here...

End With

End Sub

inquirer wrote:

I have a program which reads data into columns C-H. The number of rows
varies from case to case.
I want the user to select a starting cell from the dat in column C
I have the following which seesms clumsy but works:

rw = ActiveCell.Row
cl = ActiveCell.Column
Range("c2").Select
lastrow = Range(Selection, Selection.End(xlDown)).Cells.Count + 1
If rw < 2 Or rw lastrow - 1 Then
MsgBox "Start cell outside data range"
Exit Sub
End If
If cl < 3 Then
MsgBox " Start cell in wrong column"
Exit Sub
End If

Before exiting the sub with an error, I would like to reset the active cell
to the (incorrect) cell the user selected.

Can someone tell me how to do this please? Or suggest a better way?
Thanks
Chris

--

Dave Peterson


--

Dave Peterson

Thanks for your suggestions Dave. The code I posted was just a snippet which
made the use of lastrow look a bit silly as you pointed out.

chris

"Dave Peterson" wrote in message
...
I meant to ask why you add 1 to the lastrow only to subtract it later. I
guessed that maybe you were using it in parts of the code that weren't
posted.



Dave Peterson wrote:

Another couple of options.

You could drop the select:

Option Explicit
Sub testme2()

Dim rw As Long
Dim cl As Long
Dim lastRow As Long

With ActiveSheet
rw = ActiveCell.Row
cl = ActiveCell.Column
lastRow = .Range("c2").End(xlDown).Row + 1
If rw < 2 _
Or rw lastRow - 1 Then
MsgBox "Start cell outside data range"
Exit Sub
End If
If cl < 3 Then
MsgBox "Start cell in wrong column"
Exit Sub
End If

'more code here...

End With

End Sub

But maybe it would be simpler to just have your macro find the next cell
that
you want:

Sub testme1()

With ActiveSheet
.Cells(.Rows.Count, "C").End(xlUp).Offset(1, 0).Activate

'more code here...

End With

End Sub

inquirer wrote:

I have a program which reads data into columns C-H. The number of rows
varies from case to case.
I want the user to select a starting cell from the dat in column C
I have the following which seesms clumsy but works:

rw = ActiveCell.Row
cl = ActiveCell.Column
Range("c2").Select
lastrow = Range(Selection, Selection.End(xlDown)).Cells.Count + 1
If rw < 2 Or rw lastrow - 1 Then
MsgBox "Start cell outside data range"
Exit Sub
End If
If cl < 3 Then
MsgBox " Start cell in wrong column"
Exit Sub
End If

Before exiting the sub with an error, I would like to reset the active
cell
to the (incorrect) cell the user selected.

Can someone tell me how to do this please? Or suggest a better way?
Thanks
Chris

--

Dave Peterson


--

Dave Peterson