use of activecell

I

inquirer

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
 
C

cucchiaino

inquirer said:
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.
Click to expand...


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
 
D

Dave Peterson

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
 
D

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.
 
I

inquirer

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
 
Top