+ Reply to Thread
Results 1 to 9 of 9

Find and Replace with nested If?

  1. #1
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Find and Replace with nested If?

    I can do a find and replace in VBA, but I'm not sure how to write this one with the If thrown in.
    Basically what I need to do is: IF Column H.Value = TravelMeal OR Vehicle Shipping
    Then
    IF Column G.Value = "18 MDX" Then Update Column G.Value = "2BJ"
    Else Do Nothing.

    There are many instances where Column H is either TravelMeal or Vehicle Shipping, But I only want to change the value in Column G if it is currently 18 MDX and the former is true.

    Hope that makes sense, appreciate any help!

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Find and Replace with nested If?

    Try this:

    Please Login or Register  to view this content.
    If you are happy with my response please click the * in the lower left of my post.

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Find and Replace with nested If?

    Hi,

    Rather than using looping code in a macro which is often slow I'd be inclined to use a helper column and enter the following formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Copy this down the helper column then copy the helper range and paste it back to G1 as values.

    You could of course encapsulate that process in a simple macro to automate the same process.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Re: Find and Replace with nested If?

    I have a lot of other code in this VBA function, so I had to modify it slighty. But I'm getting a user defined error. I'm guessing I'm missing something simple.

    With Worksheets("Other Accounts")
    .Range ("A1:H" & .Range("H" & Rows.Count).End(x1Up).Row), "Travel_Meal", x10r, "Vehicle_Shipping"
    .Range ("A1:H" & .Range("H" & Rows.Count).End(x1Up).Row), "18 MDX"
    .Range("G2:G" & .Range("G" & Rows.Count).End(x1Up).Row).SpecialCells(x1CellTypeVisible).Value = "2BJ"
    End With

  5. #5
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Re: Find and Replace with nested If?

    There are about 65 different files that I'm running this code on so there's no way I can reasonably use the helper column idea. Although it is a good one, if it were only one workbook that's definitely the route I would take! Thank you.

  6. #6
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Find and Replace with nested If?

    I have a lot of other code in this VBA function, so I had to modify it slighty. But I'm getting a user defined error. I'm guessing I'm missing something simple.
    You left a lot of the stuff out from the code I supplied. Compare your code and my code for the differences.

  7. #7
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Re: Find and Replace with nested If?

    I didn't want to turn off the screen updating until I'm done. I'm not sure what the auto filter does.
    But I think I found another solution while I've been playing with it, although it does give me an error too.

    Dim i As Long
    For i = 1 To Rows.Count
    If (Cells(i, 5).Value = "Travel_Meal") Or (Cells(i, 5).Value = "Vehicle_Shipping") And Cells(i, 6).Value = "18 MDX" Then
    Cells(i, 5).Value = "2BJ"

    End If

    Next i

  8. #8
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Re: Find and Replace with nested If?

    This doesn't give me an error, but it doesn't change any values either. I'm guessing it has something to do with my parenthesis?

    Dim i As Long
    For i = 1 To Rows.Count
    If (Cells(i, 5).Value = "Travel_Meal" Or Cells(i, 5).Value = "Vehicle_Shipping" And Cells(i, 6).Value = "18 MDX") Then
    Cells(i, 5).Value = "2BJ"

    End If

    Next i

  9. #9
    Registered User
    Join Date
    10-07-2015
    Location
    Columbus, OH
    MS-Off Ver
    2007
    Posts
    35

    Re: Find and Replace with nested If?

    Nevermind, the above code works perfectly! Apparently I just don't know how to count my columns. Ha.

    Thanks again!

+ 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] Using find replace to first find two double quotes and replace with a single double quote
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-17-2015, 10:13 AM
  2. [SOLVED] Find and replace where contents a formula so can't be seen by Find and replace.
    By markDuffy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-26-2015, 02:24 PM
  3. Replies: 16
    Last Post: 12-02-2014, 03:38 PM
  4. Replies: 3
    Last Post: 11-25-2014, 06:08 AM
  5. [SOLVED] Find & replace text in a table nested within a header for a batch of word files
    By Tino XXL in forum Word Programming / VBA / Macros
    Replies: 2
    Last Post: 09-29-2014, 08:38 AM
  6. find in excel replace in word: find/replace text in text boxes and headers
    By dean.rogers in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-10-2012, 12:40 PM
  7. find and replace - replace data in rows to separated by commas
    By msdker in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-14-2006, 08:10 PM

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