+ Reply to Thread
Results 1 to 7 of 7

Find value in separate sheet, offset and replace original cell value

  1. #1
    Forum Contributor
    Join Date
    01-30-2014
    Location
    England
    MS-Off Ver
    MS Office 2010, 2007, 2003
    Posts
    149

    Find value in separate sheet, offset and replace original cell value

    Hi all

    What I'm trying to achieve is to loop through a list of cells, taking the value, for example "A", finding "A" in a separate list, offsetting to the right by 1, and using that cells value, again for example "test" to replace the original cell value from "A" to "test".

    This is the code I'm currently using:
    Please Login or Register  to view this content.
    It's adapted from something I found online but it isn't replacing the active cell.

    Any help is great

    Thanks
    Last edited by Aaron092; 09-30-2014 at 07:07 AM.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Find value in separate sheet, offset and replace original cell value

    Not sure actually what you are trying to do But I am sure it can be simply achieved in Worksheet functions.

    Just an attempt without knowing the actual requirement

    Please Login or Register  to view this content.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Contributor
    Join Date
    01-30-2014
    Location
    England
    MS-Off Ver
    MS Office 2010, 2007, 2003
    Posts
    149

    Re: Find value in separate sheet, offset and replace original cell value

    Hi SixthSense

    What I've got is a list of letters, and I need to change them to what they should be which is defined in another sheet.
    For example:
    A = test1
    B = test2
    C = test3

    So if the active cell's value is A, I need it changed to test1 or if it's C, then test3 and so on..

    I've attached a sheet for you.

    Also - I tried the code you suggested and I got a runtime error 94 on "rActive = ActiveCell"

    EESR example.xlsx

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Find value in separate sheet, offset and replace original cell value

    Maybe:

    Please Login or Register  to view this content.

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Find value in separate sheet, offset and replace original cell value

    How to install your new code
    1. Copy the Excel VBA code
    2. Select the workbook in which you want to store the Excel VBA code
    3. Press Alt+F11 to open the Visual Basic Editor
    4. Choose Insert > Module
    5. Edit > Paste the macro into the module that appeared
    6. Close the VBEditor
    7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

    Please Login or Register  to view this content.
    To run the Excel VBA code:
    1. Press Alt-F8 to open the macro list
    2. Select a macro in the list
    3. Click the Run button

  6. #6
    Forum Contributor
    Join Date
    01-30-2014
    Location
    England
    MS-Off Ver
    MS Office 2010, 2007, 2003
    Posts
    149

    Re: Find value in separate sheet, offset and replace original cell value

    Thank you very much John - your code changed each of them but the first 4 came up as 'ttestst#'

    Sixthsense yours works perfectly - thank you very much too. Reputation given to both for your help.
    (I would have posted the entire .XLSM file instead of .XLSX but the live file has around 20 modules and a lot of personal information)

    Thank you both again

  7. #7
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Find value in separate sheet, offset and replace original cell value

    Quote Originally Posted by Aaron092 View Post
    Thank you very much John - your code changed each of them but the first 4 came up as 'ttestst#'

    Sixthsense yours works perfectly - thank you very much too. Reputation given to both for your help.
    (I would have posted the entire .XLSM file instead of .XLSX but the live file has around 20 modules and a lot of personal information)

    Thank you both again
    Modified:

    Please Login or Register  to view this content.

+ 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] Macros to find matching text on other sheet and to select an offset cell
    By lottidotti in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-15-2012, 05:59 AM
  2. Help/Advice regarding Find-->offset-->replace
    By winchester427 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-28-2012, 09:49 AM
  3. Replies: 2
    Last Post: 08-28-2012, 03:12 PM
  4. Find All and Replace Offset Value
    By mccrimmon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-25-2011, 12:56 PM
  5. From cell in sheet 2 find name in sheet 1 and value offset
    By Frank345 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-05-2008, 01:50 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