+ Reply to Thread
Results 1 to 7 of 7

Macro - Lookup and Replace possibly?

  1. #1
    Registered User
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    MS Office 2016 (Home PC), MS Office (Work PC)
    Posts
    62

    Question Macro - Lookup and Replace possibly?

    Good Afternoon Folks,

    I am looking for assistance with a Macro(?) to replace values in a large spreadsheet based on a lookup table.

    Story:

    I have a maintenance programme that has all the assets down the left hand column, the fields to the right are intervals, each field represents a week in the year. Some assets have one visit and others may have more than one. Each of the visits are populated in these fields with a 3M, 6M, 12M (M=Monthly). I am looking at some assistance on how to create something that will cycle like a typewriter between these fields and replace the the 3m, 6M, 12M with a number which is essentially the duration of the maintenance visit. As the assets have different durations or frequencies I have built a table that covers this off.

    What I have:
    An example to share
    A look up table

    I hope someone can a)decipher what i am looking for and b) help to create a solution. I am going to need to use this often and the amount of rows in the plan will vary.

    Thank you for your time.

    Yours,

    GC

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Macro - Lookup and Replace possibly?

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    MS Office 2016 (Home PC), MS Office (Work PC)
    Posts
    62

    Re: Macro - Lookup and Replace possibly?

    Sample file attached, thanks.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    511

    Re: Macro - Lookup and Replace possibly?

    Hello

    Try the attached book
    I've modified a bit the structure of the LookUp Tables, just to make it easy to follow.
    Click on the Arrow to convert...
    Attached Files Attached Files
    Barriers are there for those who don't want to dream

  5. #5
    Registered User
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    MS Office 2016 (Home PC), MS Office (Work PC)
    Posts
    62

    Re: Macro - Lookup and Replace possibly?

    Hi vichopalacios,

    Thank you for this, much appreciated, I have a query.

    If I have more rows of data do I need to change anything in the code? Also, if I make a change in the named ranges it doesn't seem to change? i.e. if I add a value to door 1m (cell D6) this doesn't pull through.

    Thanks again for your assistance,

    Regards,
    GC1
    Many Thanks

    GC1

  6. #6
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    511

    Re: Macro - Lookup and Replace possibly?

    Hi GC1

    the number of rows is not a problem, you don't need to do anything
    same consideration with the number of columns, you can increase them, and the code will work anyhow.
    just note that the larger the list, the longer the time.

    You can extend down the lines in the named ranges
    just check the new lines are included in the named range, and the numbering goes on accordingly

    I tried 1M in cell D6 (it is wheel, not door), and works perfectly

    If you put 1M in cell D4 (it has door), and run the code, you will notice that the cell become empty,
    that is because you did not define the value to use instead of 1M for door, in the LookTable named range, so the code asumes that the value to replace is "" (null)

  7. #7
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    511

    Re: Macro - Lookup and Replace possibly?

    Perhaps I missunderstood the question about cell D6

    I tried a value for door 1M, in cell D6 in the lookTable named range, and 1M in cell B4...in the test sheet
    and the code works normally
    check for misstyping, remember for comparission of terms must be identical ( blank spaces, Ucase/Lcase..)

+ 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] Double Lookup - Possibly SumIfs
    By 1Stacy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-13-2018, 06:12 PM
  2. [SOLVED] Complex LOOKUP and Return Criteria possibly using INDEX/MATCH...
    By Ourkid123uk in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 04-19-2017, 08:43 AM
  3. [SOLVED] Referencing or lookup function possibly?
    By rodeam in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-17-2016, 02:31 PM
  4. Don't know where to start - lookup function with ifs possibly?
    By courtie89 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-19-2015, 03:48 PM
  5. [SOLVED] Looking for a formula - possibly V or H Lookup or MATCH?
    By Clare1234 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-17-2013, 06:53 AM
  6. possibly a lookup function question, not sure
    By mufan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-11-2008, 09:46 PM
  7. Lookup possibly?
    By SS in forum Excel General
    Replies: 3
    Last Post: 01-06-2005, 10:06 AM

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