Need code for fill a number trend down

[Sheryl]

I need to write a macro to fill Column C based on Column B. Column A has all
rows filled with text. Code needs to start reading at B2 (Row 1 contains my
headers) and enter a number trend down in Column C until it reads the next
number in Column B, then start the trend again and continue until the last
line of Column A. Column B is already filled out. I just need code to fill
Column C. Is this possible? I don't know where to begin with the code.
Your help would be greatly appreciated. I am using Excel 2002.
My spreadsheet example below.
Column B Column C
1 1
2
3
2 1
2
3 1
2
3

 

[OssieMac]

Hi Sheryl,

Hope this code answers your question. If not right then get back to me
because I am not 100% sure that I have correctly interpreted your question.
Code is based on the active worksheet.


Sub Number_Trend()

Dim rngC As Range
Dim cel As Range
Dim lngLastRow As Long
Dim lngCounter As Long

'find number of last row in column A
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row

'set range in column C from 2nd row to row matching
'last row in column A
Set rngC = Range(Cells(2, "C"), Cells(lngLastRow, "C"))

'Loop through cells in column C and set values
'based on data/no data in column B
For Each cel In rngC
If cel.Offset(0, -1).Value > 0 Then
lngCounter = 1
Else
lngCounter = lngCounter + 1
End If
cel.Value = lngCounter
Next cel

End Sub

 

[Sheryl]

Thank you so much. You interpreted it 100%. Since you are so awesome at
this, could you tell me what the code would be to take Column B, start at the
1 and fill that down with 1 until 2, then fill 2 down with 2 until 3 and so
on? Also, thanks for responding so quick too, especially on a Friday.

 

[OssieMac]

Hi again Sheryl,

I am not sure whether you want to increment the numbers in column C from 1
to whatever or whether you mean that you want them to match the numbers in
column B so I have provided both macros. The first increments from 1 and the
second matches the number found in column B. Of course if the numbers in
column B increment from 1 then it will not matter which one you use.

Sub Number_Trend_2()

Dim rngC As Range
Dim cel As Range
Dim lngLastRow As Long
Dim lngCounter As Long

'find number of last row in column A
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row

'set range in column C from 2nd row to row matching
'last row in column A
Set rngC = Range(Cells(2, "C"), Cells(lngLastRow, "C"))

'Loop through cells in column C and increment value
'in column C each time a value is found in column B
For Each cel In rngC
If cel.Offset(0, -1).Value > 0 Then
lngCounter = lngCounter + 1
End If
cel.Value = lngCounter
Next cel

End Sub



Sub Number_Trend_3()

Dim rngC As Range
Dim cel As Range
Dim lngLastRow As Long
Dim lngCounter As Long

'find number of last row in column A
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row

'set range in column C from 2nd row to row matching
'last row in column A
Set rngC = Range(Cells(2, "C"), Cells(lngLastRow, "C"))

'Loop through cells in column C and set values
'in column C based on value in column B
For Each cel In rngC
If cel.Offset(0, -1).Value > 0 Then
lngCounter = cel.Offset(0, -1).Value
End If
cel.Value = lngCounter
Next cel

End Sub