+ Reply to Thread
Results 1 to 2 of 2

Help on filling empty cells with string + row()

  1. #1
    John Smith
    Guest

    Help on 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


  2. #2
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1