Find the first instance of "1" in a column and then start numbering in another column

I need a formula that looks in a column of data for a "1" and when it finds it, starts numbering from 1 to a specified number in a different column (and stops numbering at the max limit).

So, in column A1 - A10, cell A3 content is 1 (all other cells in that column are empty). My range is 1 - 5.

The formula in column B1 - B10 would look for a 1 in column A1 - A10 and start numbering from 1 - 5 when it finds it. Like so:

A1 = "" B1 = ""
A2 = "" B2 = ""
A3 = 1 B3 = 1
A4 = "" B4 = 2
A5 = "" B5 = 3
A6 = "" B6 = 4
A7 = "" B7 = 5
A8 = "" B8 = ""
A9 = "" B9 = ""
A10 = "" B10 = ""

Originally Posted by stnkynts

Is your range always 1 - 5? Is there a set number of columns or will it be dynamic? Is is always rows 1 - 10 only?

Edit: A guess

Sub RunMe()
Dim ws As Worksheet:    Set ws = Sheets("Sheet1") 'you may need to change this
Dim iFind As Range
Dim icol As Long, iCount As Long

Application.ScreenUpdating = False

For icol = 1 To 9 'to 9th column
    iCount = 2
    Set iFind = ws.Range(Cells(1, icol), Cells(10, icol)).Find(What:="1", LookIn:=xlValues, Lookat:=xlWhole)
    If Not iFind Is Nothing Then
        Do
            iFind.Offset(iCount - 1, 0).Value = iCount
            iCount = iCount + 1
        Loop Until iCount = 6 Or iFind.Offset(iCount - 1, 0).Row = 11
    End If
Next icol

Application.ScreenUpdating = True

End Sub

I wasn't really looking for VBA code but I'll save this for the future! Thanks!

Originally Posted by martindwilson

something like this? enter value in c2

Absolutely perfect! Thanks!