+ Reply to Thread
Results 1 to 5 of 5

Adapt a macro to operate on a single column instead of entire sheet

  1. #1
    Registered User
    Join Date
    12-09-2017
    Location
    Ohio
    MS-Off Ver
    Excel 2013 (home/student)
    Posts
    60

    Adapt a macro to operate on a single column instead of entire sheet

    I have a macro that does what I want it to do (replace a series of one-letter codes with full words), but I need it to run on just one column. How do I modify it to do that? I would like it to run on column L (except for the first two rows, if possible).

    Sub Change_Status_Format()

    Dim sht As Worksheet
    Dim fndList As Variant
    Dim rplcList As Variant
    Dim x As Long

    fndList = Array("d", "c", "m", "w", "s","div")
    rplcList = Array("div", "child", "married", "widowed", "single", "divorced")

    'Loop through each item in Array lists
    For x = LBound(fndList) To UBound(fndList)
    'Loop through each worksheet in ActiveWorkbook
    For Each sht In ActiveWorkbook.Worksheets
    sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
    Next sht

    Next x

    End Sub

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,520

    Re: Adapt a macro to operate on a single column instead of entire sheet

    Give this a try

    Please Login or Register  to view this content.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Registered User
    Join Date
    12-09-2017
    Location
    Ohio
    MS-Off Ver
    Excel 2013 (home/student)
    Posts
    60

    Re: Adapt a macro to operate on a single column instead of entire sheet

    Thanks, Mike. It was almost perfect. But it didn't replace the last two rows (regardless of how many rows had data). If I change the offset value to -1, only one code is not replaced, and if I change it to 0, everything seems to work. Would doing so create any other problems? Can you explain the purpose of the offset?

  4. #4
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,520

    Re: Adapt a macro to operate on a single column instead of entire sheet

    The purpose of the Offset was because I misread your request. I thought I read except for the last 2 rows. After rereading I see that except the first 2 rows. My bad
    But to answer your question, the code was finding the last row in column L and offset 2 up.

    So change this
    Please Login or Register  to view this content.
    to this
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    12-09-2017
    Location
    Ohio
    MS-Off Ver
    Excel 2013 (home/student)
    Posts
    60

    Re: Adapt a macro to operate on a single column instead of entire sheet

    Thank you again. Perfect.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Process to Highlight Entire Row if one field matches # on a single column of a dif sheet
    By deeproots79 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-10-2016, 09:14 PM
  2. Create a macro to delete rows if column d =2529-3 for an entire sheet
    By Triscia in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-25-2015, 03:21 PM
  3. Macro for finding cell value and pasting entire column in new sheet
    By rexer231 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-15-2013, 07:20 AM
  4. [SOLVED] How to adapt winnons macro for clearing cells to work when sheet is protected
    By nje in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 02-06-2013, 01:03 PM
  5. macro to autofill entire row when a column in the sheet is changed
    By kbaruin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-12-2012, 03:44 AM
  6. adapt macro to select individual sheet rather than all sheets
    By mania112 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-14-2009, 10:18 AM
  7. Pasting single formula entire column
    By scomar411 in forum Excel General
    Replies: 3
    Last Post: 07-17-2007, 01:46 AM

Tags for this Thread

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