Help with filling empty cells with string + row()
 

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

Help with filling empty cells with string + row()
 

if cells(1,1) < "" then cells(2,1) = cells(1,1) & " " & cells(2,1).row

you can make use of a variable in the cells function.
e.g. cells(i,1)

HTH

Andrew Bourke

John Smith wrote:
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

Help with filling empty cells with string + row()
 

Hello John,

Replace this line of code...
activecell.offset(1,0).select


With...

With ActiveCell
..Offset(1, 0).Value = .Text & .Row
End With

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=561330