Help on filling empty cells with string + row()

[John Smith]

Say, A1 contains "apple", A3 "orange, A6 "berry", and A10
"banana". I would like to fill A2 with "apple 2", A4 "orange 4",
A5 "orange 5", A7 "berry 7", A8 "berry 8", A9 "berry 9". How
should I do it ?

How do I replace an empty cell with a string plus row #?


I tried the following but it did not work (forget about the loop
part because it's more complex than what I'm showing):

dim sCellContent as string

range("a1").select

if isempty(activecell) = false then
sCellContent = activecell.text
end if

activecell.offset(1,0).select

do while isempty(activecell) = false

activecell.text = sCellContent&" "&row()

loop

 

[Mallycat]

try this.

First select the range then run this macro

Sub copydown()
Dim myRange As Range
Set myRange = Selection
myText = ""
For Each cell In myRange
'set a new text value to use when it finds a non blank cell
If cell.Value <> "" Then
myText = cell.Value
Else
cell.Value = myText & cell.Row
End If
Next cell
End Sub