Loop through rows using Excel VBA to set a incrementing counter

Question

The "REQUIRED" field is the one which needs populating,

For each UNIQUE Serial "REQUIRED" Number incremented by 1 ONLY IF starred "*". in VBA.

This is what it should look like.

http://s2.postimg.org/nketog5t5/table.jpg

Serial Number   REQUIRED    Starred SEQ_NO
040846/Z/96/C   1   *   1
040846/Z/96/C   2   *   2
040846/Z/96/C           3
042190/Z/96/F   1   *   1
042368/Z/97/B   1   *   1
042368/Z/97/B           2
042368/Z/97/B   2   *   3
042368/Z/97/B           4
042368/Z/97/B   3   *   5
042368/Z/97/B           6
042368/Z/97/B           7
042368/Z/97/B           8
047608/Z/96/B   1   *   1
047608/Z/96/B           2
047675/Z/96/D   1   *   1
047675/Z/96/D   2   *   2
047675/Z/96/D           3

I don't think you need VBA for this. I think you can accomplish it by using COUNTIFS() formula. — Marc
– Marc, Commented Aug 11, 2015 at 12:01
Can you please be a little more clear? You have incremented numbers on some values without a "*". — GisMofx
– GisMofx, Commented Aug 11, 2015 at 12:04

Marc · Accepted Answer · 2015-08-11 12:07:12Z

2

Paste this formula down the column you're trying to populate. You can tweak it a little to hide the zeroes if you care about that.

=COUNTIFS($A$2:A2,A2,$C$2:C2,"*")

Note the hard references vs. the relative references in that formula, and make sure your entries align with those.

answered Aug 11, 2015 at 12:07

Marc

11.6k2 gold badges40 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nitish · Accepted Answer · 2015-08-11 12:41:04Z

1

Please try this

Sub increment()
Dim prevalue As String
Dim curvalue As String
Dim lstrow As Long, i As Long, j As Integer

lstrow = Range("A" & Sheets("Sheet1").Rows.Count).End(xlUp).Row
j = 0

For i = 2 To lstrow
    curvalue = Range("A" & i).Value
    If curvalue = prevalue And Range("A" & i).Offset(0, 2).Value = "*" Then
        j = j + 1
        Range("A" & i).Offset(0, 1).Value = j
    ElseIf curvalue <> prevalue And Range("A" & i).Offset(0, 2).Value = "*" Then
        j = 1
        Range("A" & i).Offset(0, 1).Value = j
    End If
    prevalue = curvalue
Next i
End Sub

answered Aug 11, 2015 at 12:41

Nitish

3411 silver badge7 bronze badges

1 Comment

Jeff Palson Over a year ago

Very much so, thanks Nitish this code was very useful for me to learn how to do things like prevalue Offset which i wasn't sure how it was done.

Collectives™ on Stack Overflow

Loop through rows using Excel VBA to set a incrementing counter

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related