+ Reply to Thread
Results 1 to 2 of 2

Inserting text based on a predetermined criteria early in the macro

Hybrid View

  1. #1
    Registered User
    Join Date
    05-22-2012
    Location
    Wichita, Kansas
    MS-Off Ver
    Excel 2003
    Posts
    6

    Inserting text based on a predetermined criteria early in the macro

    I have received a macro via another thread in this forum that insert a row for me between groups of list in my spreadsheet. I would like it to paste the title of that list at the beginning of the inserted row but currently it only copies 'repeat guest' in every cell not customizing it to the column.

    How do I go about doing this? Here is the macro and sample spreadsheet.

    Sub macro1()
    Dim c As Range
    Dim LRow

    'variable LRow equal to last row with data in column A
    LRow = Cells(Rows.Count, "A").End(xlUp).Row
    'for each cell in column D to the last row that contains data
    For Each c In Range("D2:D" & LRow)
    'if the cell is not equal to the one below it
    'and the cell below it is not equal to 0
    'and the cell is not equal to zero then
    If c.Value <> c.Offset(1, 0).Value And c.Offset(1, 0).Value <> vbNullString And c.Value <> vbNullString Then
    'select the cell below the activecell
    'and insert a row above it
    c.Offset(1, 0).EntireRow.Select
    Selection.Insert Shift:=xlDown

    'set the value in column A of the new row equal to Repeat Guests and bold it.
    With c.Offset(1, -3)
    .Value = "Repeat Guests"
    .Font.Bold = True
    End With
    End If
    Next
    End Sub
    Sample Guest List.xls

  2. #2
    Forum Contributor wallyeye's Avatar
    Join Date
    05-06-2011
    Location
    Arizona
    MS-Off Ver
    Office 2010, 2007
    Posts
    308

    Re: Inserting text based on a predetermined criteria early in the macro

    Which column would the title be in?

    You could replace the .Value statement with:

    .Value = c.offset(2,4).value

    Which would look two rows down and four to the right (column H) for the title and place it in column A of the inserted row.

+ 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