+ Reply to Thread
Results 1 to 3 of 3

Replace sub based on 2 criteria

  1. #1
    Forum Contributor
    Join Date
    04-30-2010
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    215

    Replace sub based on 2 criteria

    Hello all

    Im hoping someone can guide me in the right direction with the best approach for the following:
    I would like to make a dynamic sub, which will run through a sheet called RenamingSheet and then replace certain cells in other sheets based on the values/criterias mentioned. The RenamingSheet is set up like below:
    RenamingSheet:
    RenamingSheet.png

    The other 2 sheets I made in the demo file look like this:
    Sheet1:
    Sheet1.png

    Sheet2:
    Sheet2.png

    The RenamingSheet contains all info to find a specific cell in the other sheets based on 2 criterias. So for instance, with the second row, that looks like this:
    Renamingtop.png

    the script should find the row in Sheet1 (A) that matches the 2 criterias where SheetColumn1 (C) is Green (D) and SheetColumn2 (E) is Apple (F), and then change SheetColumn3 (B) to 10 (H). There will always only be 1 match and in this case it would be C2 in Sheet1. So the value in [Renaming.xlsx]Sheet1!$C$2 should be changed from 1 to 10.

    I made a formula to find that cell (G), but not sure what approach is best for this sub. Wondering if its should be a combo of Evaluate and Find in VBA or if there is some other clever way to make this. I need a dynamic solution, as the values will change repeatedly and I will need to run the replace sub often.

    Appreciate any suggestions to the best approach for this!

    Edit: Just for clearification, the headers and sheetnames are just made up for this demo - its not called stuff like Sheet1Column1 in my actual setup. Its just to make this demo more transparent. Would just mention that, in case someone was thinking of a solution depending on those specific names.
    Attached Files Attached Files
    Last edited by Imbizile; 02-22-2018 at 06:03 AM.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Replace sub based on 2 criteria

    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Forum Contributor
    Join Date
    04-30-2010
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    215

    Re: Replace sub based on 2 criteria

    Amazing - just amazing... does exactly what I was looking for!

    Will try to drill down into your code to fully understand it

    Thanks so much Bernie - really appreciate your effort.

+ 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] Replace ~ in a string according to criteria
    By wali in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-10-2015, 12:03 PM
  2. [SOLVED] Which formula can I use to replace data based on various criteria?
    By acousticsman in forum Excel General
    Replies: 7
    Last Post: 06-13-2015, 05:03 PM
  3. [SOLVED] Replace values in rows based on criteria in column B
    By Marq555 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-03-2015, 10:25 AM
  4. Find and replace multiple values based on criteria
    By samuelhzb in forum Excel General
    Replies: 6
    Last Post: 01-04-2015, 01:25 PM
  5. Criteria based find and replace
    By doubl3d80 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-17-2014, 12:19 PM
  6. Find and replace with criteria
    By Jhon Mustofa in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 01-07-2014, 01:52 AM
  7. [SOLVED] Find blank cell and replace based on criteria
    By anrichards22 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 11-27-2012, 05:36 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