Results 1 to 7 of 7

VBA to search for cell and replace value

Threaded View

DaBrot791 VBA to search for cell and... 08-09-2016, 07:59 PM
Winon Re: Newbie Help needed : VBA... 08-09-2016, 08:35 PM
DaBrot791 Re: Newbie Help needed : VBA... 08-09-2016, 09:45 PM
DaBrot791 Re: VBA to search for cell... 08-10-2016, 01:00 AM
Winon Re: VBA to search for cell... 08-10-2016, 06:46 AM
DaBrot791 [Solved] VBA to search for... 08-10-2016, 07:01 PM
Winon Re: VBA to search for cell... 08-10-2016, 07:54 PM
  1. #1
    Registered User
    Join Date
    09-04-2012
    Location
    Seoul, South Korea
    MS-Off Ver
    Excel 2007
    Posts
    20

    VBA to search for cell and replace value

    This is an extension of my post found here, but trying to approach the problem from a different perspective.
    http://www.excelforum.com/showthread...t=#post4440273
    Based on extensive internet searching and studying VBA (first venture into this world) I have found a code to find a value
    But now I need to develop it in two big ways.
    1) End result should be to replace the value with another value in a different cell, different column but same row (i.e.("f6").value replace with range("i6").value
    2) As this record is repeating, it should loop through the search, redefining "i" and "findstring" and searching until "findstring"="" (i.e. search based on f6, after calculation is complete, search based on f16, f26, etc.)
    The code, which I lightly modified, is as follows: (any advise for improving speed or reliability is appreciated. Total newb)

    Private Sub ButtonFind()
    'adaptation from scott's code
    'http://stackoverflow.com/questions/12642164/check-if-value-exists-in-column-in-vba
    
    Dim FindString As String 'defines value to be found
    
    Dim Rng As Range
    
    Dim Lan As String 'defines language selected
    Lan = Sheet2.Range("e9").Value
    
    Dim i As Integer 'defines step value for records. current value is recorded in f(6+10*i)
    
    i = 6
    FindString = Sheet3.Range("f" & i).Value 'stored value occurs once every ten rows in "f"
    
    If Trim(FindString) <> "" Then
        If Lan = Sheet1.Range("KOR").Value Then
            With Sheet6.Range("ClashIssuesEng") 'searches all of ClashissuesEng range
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True 'value found (need to replace this formula with a replacement cell value based on this values row location)
            Else
                MsgBox "Nothing found" 'value not found
            End If
            End With
        End If
        If Lan = Sheet1.Range("ENG").Value Then
            With Sheet6.Range("ClashIssues") 'searches all of Clashissues range
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True 'value found (need to replace this formula with a replacement cell value based on this values row location)
            Else
                MsgBox "Nothing found" 'value not found
            End If
            End With
        End If
    'i = i + 10 needs to loop until findstring=""
    End If
    
    End Sub
    Last edited by DaBrot791; 08-09-2016 at 09:47 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Search and replace in the first part ONLY of cell
    By researchass in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-03-2014, 02:47 PM
  2. Search and replace character with cell value
    By rfcapinto in forum Excel General
    Replies: 2
    Last Post: 03-24-2014, 05:31 PM
  3. Replies: 5
    Last Post: 05-13-2013, 06:23 PM
  4. Run a search and replace based on what is in another Cell?
    By cekenney in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-28-2012, 03:17 PM
  5. [SOLVED] Excel Search and Replace help needed
    By Blueness in forum Excel - New Users/Basics
    Replies: 13
    Last Post: 07-19-2012, 10:01 AM
  6. Search Column for value in Cell & Replace
    By janglin in forum Excel General
    Replies: 5
    Last Post: 08-22-2006, 11:00 PM
  7. [SOLVED] Newbie trying to search and replace word in word doc from excel
    By kaiser in forum Excel General
    Replies: 5
    Last Post: 09-13-2005, 07:06 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