+ Reply to Thread
Results 1 to 3 of 3

Searching, displaying and returning a value

  1. #1
    Registered User
    Join Date
    08-07-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Searching, displaying and returning a value

    Hello everyone! I am new in VBA and need some help with my coding.

    I have a program which searches for similar words in the workbook when I enter a word in any cell, and then replaces it (for example, I type "Marke" and then program finds next similar word to it "Market" and automatically replaces "Marke" to "Market".

    However what I need to have ideally is a popping up box with the list of similar words found, so I can click on the one I need to be returned in the original cell where I first enetered a word
    (In the example above after enering a word "Marke" a listbox would popup with words such as "Market", "Market rules", "Entereing the Market", and then I can click on, say, "Market rules", and it will replace the original "Marke" to "Market rules".
    Any ideas how I can do it? Any help would be appreciated!

    Here is what I have so far:

    Sub Macro14()
    Dim datatoFind
    Dim sheetCount As Integer
    Dim counter As Integer
    Dim currentSheet As Integer
    Dim PrevCell As Range
    On Error Resume Next
    currentSheet = ActiveSheet.Index
    datatoFind = ActiveCell.Value
    If datatoFind = "" Then Exit Sub
    sheetCount = ActiveWorkbook.Sheets.Count
    If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
    For counter = 1 To sheetCount
    Sheets(counter).Activate
    Set PrevCell = ActiveCell
    Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, Lookat _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False).Activate
    ‘If ActiveCell.Value Like "*" & datatoFind & "*" Then MsgBox "Value found"
    'PrevCell.Value = ActiveCell
    Next counter
    If ActiveCell.Value <> datatoFind Then
    MsgBox ("Value not found")
    Sheets(currentSheet).Activate
    End If
    End Sub

  2. #2
    Forum Contributor
    Join Date
    08-11-2012
    Location
    bengalur
    MS-Off Ver
    Excel 2003, 2007
    Posts
    152

    Re: Searching, displaying and returning a value

    If basically if you need to search and replace the words, you can use CTRL + F which will help you in doing the task.

    I hope you might have recorded the macro.

    Please let me know
    _______________________________________________________________________________________________
    click * if some one has helped you

    “I have not failed. I've just found 10,000 ways that won't work.”
    ― Thomas A. Edison

  3. #3
    Registered User
    Join Date
    08-07-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Searching, displaying and returning a value

    Thank you for your reply, shyamhappy.
    I was trying to run a Macro using CTRL + F, but it doesn't give a me a list of all similar words to the one I first enetered, so I can click and choose the one I need and return that value back to the cell
    Any other thoughts?

+ 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. Need help in searching certain keywords from a array and displaying them
    By hone in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 06-16-2013, 03:28 PM
  2. Searching and displaying values
    By cikaybe in forum Excel General
    Replies: 1
    Last Post: 07-08-2011, 07:22 AM
  3. Searching & Displaying data
    By greekboyuk in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-08-2010, 01:56 PM
  4. [SOLVED] searching cells - displaying data
    By davemel in forum Excel General
    Replies: 1
    Last Post: 11-02-2005, 06:17 PM
  5. Searching and displaying the result using VBA
    By kevinhd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-01-2005, 04:12 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