# How to increment counter and use result in text string

#### Xceller

##### Active Member
Hi - I have a data set that I need to fill down 4000 rows. I want the code to go down and find the name "January" then write Company1 on the 1st finding and Company 2 on the 2nd finding... Can you guys take a look at my code below, I am not placing the counter in the right place:
Thank you for your help!

Sub Fill()
nR = Cells(Rows.Count, 4).End(xlUp).Row

counter = 1
For i = 15 To nR
Select Case Cells(i, 4)
Case "January"
Cells(i, 4).Offset(0, -1) = "Company" & ncounter
End Select
Next i
ncounter = counter + 1

End Sub

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### Rick Rothstein

##### MrExcel MVP
I am not placing the counter in the right place:
So we don't have to go back-and-forth guessing... where would the right place be for the counter?

#### rjwebgraphix

##### Well-known Member
I don't use case or offset. My preferences is to read in plain english so to speak what code is doing and not having to figure it out so much.

This should do what you need.

Code:
``````Sub Fillit()

Dim lr As Long
Dim i As Long
Dim cnt As Long
lr = Cells(Rows.Count, "D").End(xlUp).Row
cnt = 1
For i = 15 To lr
If Cells(i, "D") = "January" Then
Cells(i, "C") = "Company" & cnt
cnt = cnt + 1
End If
Next i

End Sub``````

Also, in your code it should have worked fine if you moved this line...

ncounter = counter + 1

just after this line...

Cells(i, 4).Offset(0, -1) = "Company" & ncounter

but changed it to ..

counter = counter + 1

#### Xceller

##### Active Member
Hi rjwebgraphix and Rick,

Thank you for your help. rjwebgraphix - I used your code and your of edit of my code, they both both work perfectly. I guess the take away is that the initiation of the counter has to set at the beginning and outside the loop and the increment has to be within the loop. In this example i want to write it back to the adjacent cells as follows: Company1, Comapny2, Comapny3....

Thanks again for your help.

#### rjwebgraphix

##### Well-known Member
I don't know about "take away". A count has to start somewhere and in this case, you wouldn't want to start the count in the loop because it would reset the count after each time through the loop, so you have to start the count outside and before the loop.

Then of course, to increment the count, you would need it inside the loop and inside the IF statement. Outside the if statement it would just count the number of rows. Inside the if statement it will count the number of times "January" is found in Column D.

Last edited:

Replies
1
Views
255
Replies
1
Views
216
Replies
0
Views
187
Replies
4
Views
158
Replies
26
Views
699

Threads
1,196,027
Messages
6,012,950
Members
441,740
Latest member
abaz21

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back