Sequential Numbering down to last non blank cell

[Podder1965]

Hi,
Is it possible to get sequential numbering in column A starting at Cell A2 (6 consecutive numbers 206240) down to the last Non-blank cell in column B, using vba?
So if cell A2 = 206240
Then cell A10 = 20648

 

[kanadaaa]

Try this in a standard module:

Sub SeqNum()
    Dim i As Integer, lastRow As Long
    lastRow = Range("B" & Rows.Count).End(xlUp).Row
    For i = 2 To lastRow
        Range("A" & i).Value = 206240 + i - 2
    Next i
End Sub

 

[Podder1965]

Kanadaaa,
This will work, however, the value 206240 will change, is it possible to have any 6 digit number?

 

[kanadaaa]

Kanadaaa,
This will work, however, the value 206240 will change, is it possible to have any 6 digit number?

In what cell does that number appear? Just in A2?

 

[Podder1965]

Kanadaaa,
When data is generated into the worksheet it comes from another destination, as such the last number in the destination +1 is then put into cell A2, as mentioned.
So I require this number to be worked from each time (Yes, it will always be in cell A2).

 

[Rick Rothstein]

Give this macro a try...

Sub SequenceA1ValueToLastRowInColumnB()
  With Cells(Rows.Count, "B").End(xlUp)
    Range("A2:A" & .Row) = Evaluate("ROW(A" & [A2] & ":A" & .Row + [A2] & ")")
  End With
End Sub

NOTE: This macro is not dependent on the number in cell A2 being 6 digits long, so if it is possible for that number to be entered otherwise, then you might want to add an If..Then statement to insure the number's size.

 

[kanadaaa]

Just try the code below and let me know if it doesn't work the way you want.

Sub SeqNum()
    Dim i As Integer, lastRow As Long, msg As Long
    lastRow = Range("B" & Rows.Count).End(xlUp).Row
    If Len(Range("A2")) = 6 And IsNumeric(Range("A2")) Then
        For i = 3 To lastRow
            Range("A" & i).Value = Val(Range("A2")) + i - 2
        Next i
    Else
        msg = MsgBox("The value in A2 must be a 6-digit number to run this macro.", vbExclamation, "Error")
    End If
End Sub

 

[Podder1965]

Kanadaaa, Rick
Thanks, both codes worked perfectly.
Thanks for your time.