Posts by Chongo

    Re: specifying a search with a forms box.


    Sorry for the like 6 month delay but I have been very busy with school. How could you write a code to do what you are saying. That is exactly what I want to do but I am having trouble with adding the items to a combobox and displaying it.

    Re: specifying a search with a forms box.


    That is excellent. That is exactly what I wanted but is there a way to have the list drop right when you hit the sumbmit button instead of having a blank box being displayed.

    I have written a code that acts as a directory search engine for peoples names. There is a form called search that is loaded and asks the user which name he or she would like to search for. When the user enters the data, whether the name is spelled right or not, the search finds the first closest match to whatever was entered into the form. I would like to change this in one way. Many times there are people with the same first name but different last name or two very similar first names that might be highlightes instead of the exact name the user is looking for. I would like to ask if anyone knows how to write a code that would insert each of the names that have all or part of the entered name into a forms box to where a final selection could be made to select the exact row of cells that the user wants to see. This is much like the find feature on your PC. You enter what you want to find and it brings up all of the things that include your keywords and you make the final selection. Is this possible or just very complicated?
    This is the current search code that I have written. It will find the closest match but will not insert it into a form but it is the best I can do to help with this project, because I am still learning the VBA language.

    Re: Translating a search code


    I would like to have a pop-up forms box that you can click on or use the arrows on your keyboard to select one of four or five or maybe even more matches that the search found.

    Re: Translating a search code


    I just decided to rewrite the code from scratch. Thanks for all of the advice, I really appreciate it. I would Like to add something extra though. Sometimes there are more than one people with the same first name. My search highlights the first cell that closely matches my search. Is there a way to have a box or something to choose the appropriate name from?

    Re: Translating a search code


    I have managed to figure out all of the code exept for these lines. I just do not understand the column(5) if I am searching column(4). I also do not understand the is nothing stuff. I have also noticed that if you change the column (5) the code still works the same way, why?

    Code
    Do 
            Rows(rCell.Row).Activate 
            Set rCell = Columns(5).FindNext(rCell) 
        Loop While Not rCell Is Nothing And rCell.Address <> firstaddress And rCell.Row

    Re: Translating a search code


    It may be easier if I explain to you what I am trying to accomplish. I am trying to make a serach code to search a long list of names in column(4) or column D. I have a userform called search where you can enter what name you want to search for. It will select the row that has the nearest description to the name that you entered. Some of the names are very long and are more than one word. It may be easier if I just ask for help writing a code to accomplish this that is simple and easy to understand. The code that I have placed above works but has a lot of lines that I do not understand and probably are not needed. I understand a lot of VBA but I am cofused when it comes to using the find, I appreciate the link to help understand this a little better.

    Re: Translating a search code


    I do not understand why there are colums(4) and columns(5). Could someone explain these lines to me. I have found that someone explaining code builds a great understanding of VB and gives an excellent example.

    Code
    Set rCell = Columns(4).Find(strData, After:=Range("D1"), LookIn:=xlValues)
        If Not rCell Is Nothing Then 
            firstaddress = rCell.Address 
            Do 
                Rows(rCell.Row).Activate 
                Set rCell = Columns(5).FindNext(rCell) 
            Loop While Not rCell Is Nothing And rCell.Address <> firstaddress And rCell.Row


    I have modified the above search code to fit my needs but I still do not understand why some parts of the above code are witten, could someone please explain each of the above lines to me out next to each line of code?:confused:

    Re: Search a column for closest match?


    That Is exactly what I am looking for but for some reason I keep getting a debug error in my code. I am using a longer name in the street category. I t seems to be causing errors when I try to enter a more defined search. For example if the street was South Cherry Elm Street and I typed in South Cherry Elm, I get an error. How can I correct this?

    Re: Search a column for closest match?


    That is close to what I am looking for but it does not seem to work very efficiently for example if I enter paer into the search it shold find the cell with pear right? But it doesnt, it finds the row with apples, could someone help me fix this code. If there is more than one cell that closely mathes that cell I would like to be notified all of the cells that closley match the text. It might be easier to display all of the closely related names on a seperate sheet. For example, if someone enters oranges and their are two different cells that contain the words oranges, tangelos and oranges, navels then it would display both rows on the next sheet allowing the user to select whichever one they wish to select.

    I want to run about 6 macros right after each other. I have tried this over and over and it only wants to run the first macro.

    Code
    Sub Run()
    Call Macro1
    Call Macro2
    Call Macro3
    Call Macro4
    Call Macro5
    Call Macro6
    End Sub

    For some reason I can not get it to run them all. What can I do to fix this?

    Re: writing an equation instead of a macro


    I am trying to get the value of the combobox to enter in the next sheet on the next available cell in column 3. The whole code looks like this

    Re: writing an equation instead of a macro


    Code
    If p > "" Then
        ActiveSheet.DropDowns.Add(Left:=ActiveCell.Left, Top:=ActiveCell.Top, Width:=ActiveCell.Width, Height:=ActiveCell.Height).Select
        With Selection
            .ListFillRange = "'Answer Selection'!B2:B5"
            .LinkedCell = ActiveSheet.Next.Cells(i, 3)
            .DropDownLines = 4
            .Display3DShading = True


    My code looks like this and I keep getting an error on the .linked cell because it is written in vba instead of an equation, could someone help me rewrite this code. Don't worry about the variable I, that is defined in another part of the code.

    Re: Insert a picture into a range


    I am having some problems getting my cose to work. I would like to insert a picture one cell after the last populated cell in column A. I have tried this but it does not work.

    Re: writing an equation instead of a macro


    I am creating a macro that inserts a listbox into a range of cells. The value of i is the value of a certain cell. for example if the value is 1 the cell would be linked to C1 on the next sheet. I need to have the linked cell property in an equation though instead of a macro. I know how to write it in Vba but not as an equation.