Command button
 

Hi

If I have the following macro running in a workbook and I
add titles to cells A1 B1 C1 & D1 the first number will
appear in cell A2 when I click the command button however
the next number appear in cells D3, D4, D5 etc ?
Any suggestions welcome.


Option Explicit
Sub RCA()

ActiveSheet.UsedRange
If Range("A2").Value = "" Then
Range("A2").Value = InputBox("Enter starting
value")
Else
Range("A2").SpecialCells
(xlCellTypeLastCell).Offset(1, 0).Value =
Application.WorksheetFunction.Max(Range("A:A")) + 1
End If
End Sub


Thanks

Mark

By using SpecialCells(xlCellTypeLastCell) you're going to the last
"used" cell (whether with a value or formatting) - i.e, the cell in the
farthest right used column (D) and the farthest down used row (e.g, 2).
It sounds like you've already got something in D2, so the .Offset(1, 0)
will put the new data in D3.

Where do you want the number to appear?

In article ,
"Mark" wrote:

Hi

If I have the following macro running in a workbook and I
add titles to cells A1 B1 C1 & D1 the first number will
appear in cell A2 when I click the command button however
the next number appear in cells D3, D4, D5 etc ?
Any suggestions welcome.


Option Explicit
Sub RCA()

ActiveSheet.UsedRange
If Range("A2").Value = "" Then
Range("A2").Value = InputBox("Enter starting
value")
Else
Range("A2").SpecialCells
(xlCellTypeLastCell).Offset(1, 0).Value =
Application.WorksheetFunction.Max(Range("A:A")) + 1
End If
End Sub

I would like the numbers to start in A2 then A3 Then A4
etc

thanks
-----Original Message-----
By using SpecialCells(xlCellTypeLastCell) you're going
to the last
"used" cell (whether with a value or formatting) - i.e,
the cell in the
farthest right used column (D) and the farthest down
used row (e.g, 2).
It sounds like you've already got something in D2, so
the .Offset(1, 0)
will put the new data in D3.

Where do you want the number to appear?

In article ,
"Mark" wrote:

Hi

If I have the following macro running in a workbook
and I
add titles to cells A1 B1 C1 & D1 the first number
will
appear in cell A2 when I click the command button
however
the next number appear in cells D3, D4, D5 etc ?
Any suggestions welcome.


Option Explicit
Sub RCA()

ActiveSheet.UsedRange
If Range("A2").Value = "" Then
Range("A2").Value = InputBox("Enter starting
value")
Else
Range("A2").SpecialCells
(xlCellTypeLastCell).Offset(1, 0).Value =
Application.WorksheetFunction.Max(Range("A:A")) + 1
End If
End Sub
.

ONe way:

Public Sub RCA()
If IsEmpty(Range("A2").Value) Then
Range("A2").Value = InputBox("Enter Starting Value")
Else
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = _
Application.Max(Range("A:A"))+1
End If
End Sub

this assumes that there's nothing in column A below the cells you're
filling



In article ,
"mark" wrote:

I would like the numbers to start in A2 then A3 Then A4
etc

Thanks for this.
if i wanted to add some letters before the number how
would i go about this.

Thanks once again
mark

-----Original Message-----
ONe way:

Public Sub RCA()
If IsEmpty(Range("A2").Value) Then
Range("A2").Value = InputBox("Enter Starting
Value")
Else
Range("A" & Rows.Count).End(xlUp).Offset(1,
0).Value = _
Application.Max(Range("A:A"))+1
End If
End Sub

this assumes that there's nothing in column A below the
cells you're
filling



In article ,
"mark" wrote:

I would like the numbers to start in A2 then A3 Then
A4
etc
.