Solved: Fill contents of blank cell based on cell value above

[austenr]

I need to fill blank cells in column A with a string. However, lets say you have the number 12345 in cell A1. Directly under that cell if the cell is blank, I want to put "DEP 1". I can do that with no problem. The problem is that the next blank cell and every cell below it should say "DEP 2", DEP 3", etc. until you encounter another number. When that happens, I need to start over with "DEP 1". Any suggestions? Thanks :banghead: :banghead: :banghead:

[Killian]

One way to do this would be to increment a counter and each time you detect a blank cell, increment the counter and populate the celliCount = iCount + 1
Range(rngTarget).Text = "DEP " & iCountIf you do this all in one process, then having iCount declared in the procedure will be fine, but you will need to increase it's scope to Public if you want it to retain it's value between seperate procedure calls.
If you still need to go further, and want to keep the count value between sessions of that workbook so you can pick up the numbering where you left off, then you'll either need to store it somewhere (hidden sheet, reg key) or knock up a function that returns the existing instances of ColA cells containing "DEP".

/EDIT Hold on, I didn't read the question properly - I'm editing this post now :doh

[austenr]

Here is my code which works as I want it to. Can anyone suggest a way to improve it?


Sub fixblanks()
Dim i As Integer, FinalRow As Long, iCounter As Integer
FinalRow = Cells(65536, 1).End(xlUp).Row

For i = 1 To FinalRow
If Cells(i, 1).Value > 0 Then
iCounter = 1
GoTo 0
Else
If Cells(i, 1).Value = "" Then
Cells(i, 1).Value = "DEP " & iCounter
iCounter = iCounter + 1
End If
End If
0
Next i
End Sub

[Killian]

OK well I've taken a slightly different approach...
If I understand correctly, as you go dowm the column, the numbering needs to increment for each instance of each number encountered.
So if you have:
123

123

234

123

234

You will get:
123
DEP 1
123
DEP 2
234
DEP 1
123
DEP 3
234
DEP 2

Correct?

[austenr]

Well not exactly. Here is abetter example:

123
DEP 1
DEP 2
DEP 3
234
DEP1
Dep 2
DEP 3
345
456
567
Dep 1
DEP 2

The numbers only exist once. No dups. HTH

[Killian]

Ahh well in that case, what you have seems just fine, except you don't need to the goto 0

[austenr]

:banghead: . Ya stupid mistake. Oh well thanks for the help. Solved.