Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 . |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|