adding question numbers

ashleywanless

Board Regular
Joined
Aug 4, 2009
Messages
158
Hi,

I have a macro which produces a worksheet of specific questions according to what criteria a user choses on a userform. It then copies this data into a new tab. There are 3 columns, Question Number, Question and Evidence.

My problem is that because the data copied is a mere extract of a much larger question set the question numbers in column A do not run concurrently. e.g 1,2,5,6,7,9,10 etc etc. I need to find a way of making these numbers in the extract sheet run concurrently. This can't be a simple as typing 1 in cell A3, 2 in cell A4, 3 in cell A5 then sending down the page as the copied data contains grey header lines. These do not have a question number in column A instead they have a H.

Can anyone think of how to do this? so that the numbers in cells A3 onwards run concurrently unless the Value H is anywhere in column A, in which case the H should be left and the next cell should continue the numbering sequence?

Thanks

Ashley
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
If I understand correctly, try

Code:
Sub test()
Dim FinalRow As Long
FinalRow = Range("B" & Rows.Count).End(xlUp).Row
With Range("A3:A" & FinalRow)
    .Value = Evaluate("ROW(" & .Address & ")-2")
End With
End Sub
 

ashleywanless

Board Regular
Joined
Aug 4, 2009
Messages
158
Hi thanks for the reply.

The code works but doesn't take into account that if column
C has the value H it should miss that row and put the next sequential
number in the next row
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Try

Code:
Sub test()
Dim FinalRow As Long, i As Long, j As Integer
FinalRow = Range("B" & Rows.Count).End(xlUp).Row
For i = 3 To FinalRow
    If Range("C" & i).Value <> "H" Then
        j = j + 1
        Range("A" & i).Value = j
    End If
Next i
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,095,926
Messages
5,447,329
Members
405,447
Latest member
WPY

This Week's Hot Topics

Top