Auto increment numbering based on a cell value

[stirlingmw]

Good morning all
I have a worksheet "Quiz" which displays random questions taken from a question bank. These questions are annotated into column B4 onwards, based on the number of questions requested. I am trying to number these questions using VBA, but cannot find any code which seems to work.
I have left Column A bank when the questions are added to the worksheet, if any text is present in cell in column B i would like column A to show a sequential number from 1 to n dependant on the number questions generated, so if Text is present in B4, Cell A4 shows 1, B5 text is present, A5 show 2 etc. There will never be a gap in column B as all questions are annotated into the next available blank row.

TIA

Steve

 

[GirishDhruva]

Try with this,

With Range("A4:A" & Range("B" & Rows.Count).End(xlUp).Row)
    .Cells(1, 1).Value = 1
    .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Trend:=False
End With

Regards,
Dhruva

 

[stirlingmw]

Dhruva

Works perfect thank you.

I have also just figured that if i use the following code attached to the worksheet it also works.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    If Not Intersect(Range("B4:B100"), Target) Is Nothing Then
        For Each cell In Intersect(Range("B4:B100"), Target).Cells
            If cell.Value <> "" And Range("A" & cell.Row).Value = "" Then
                Range("A" & cell.Row).Value = Application.Max(Range("A4:A100")) + 1
            End If
        Next
    End If
End Sub

Your solution is slicker. thanks again

 

[GirishDhruva]

Thanks for the feedback.