Solved

Use Excel VBA .find function

Posted on 2010-09-16
5
544 Views
Last Modified: 2012-05-10
Hi experts,

I'm using a form where there is 3 textboxes. One of them (textbox2) is containing a # I'd like to find in the spreadsheet (within column "E" in fact) which will give me the row I need to update. This number is an integer but can be large (up to than 1,000,000).

I thought that I would be able to record a macro using "Ctrl+F" but it doesn't record anything. I found on the web the ".find" function but it's not working since it always returns "nothing" even if the value I'm searching is in the column.

What is wrong with my find function?

Is there anything else wrong in my code?

Anybody can give me a link explaining clearly the ".find" function and its arguments or tell me a better way to achieve this?

Thanks,
Sub Sag_01()



Dim a As String

Dim b As String

Dim c As String

Dim d As Double



    a = Userform1.TextBox1.Text    'Emplacement

    b = Userform1.TextBox2.Text    'Code

    c = Userform1.TextBox3.Text    'Quantité

    

    With ThisWorkbook.ActiveSheet.Range("E1:E50000")

        d = CDbl(b)

        Set m = .Find(d)

        If Not m Is Nothing Then

            ActiveCell = m.Address

            ActiveCell.Offset(0, 3) = a

            ActiveCell.Offset(0, 1) = b

            ActiveCell.Offset(0, 1) = c

        Else

            MsgBox ("Allo SAG")

        End If

    End With

    

End Sub

Open in new window

0
Comment
Question by:gahute
5 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 33697592
You'll find a good resource at http://www.ozgrid.com/VBA/find-method.htm

One thing to remember is that .find will remember the last used parameters, which means you should always specify what parameters to use in vba to ensure consistent behavior (where to look, what to look for, match case, etc.)

Any questions, keep posting

Thomas
0
 
LVL 6

Expert Comment

by:UsamaFoad
ID: 33697786

From Microsoft Visual Basic Help

expression.Find(Target, StartLine, StartColumn, EndLine, EndColumn, WholeWord, MatchCase, PatternSearch)
expression    Required. An expression that returns one of the objects in the Applies To list.

Target   Required String. A string expression that evaluates to the text that you want to find.

StartLine   Required Long. The line on which to begin searching. If a match is found, the value of the StartLine argument is set to the line on which the beginning character of the matching text is found.

StartColumn   Required Long. The column on which to begin searching. Each character in a line is in a separate column, beginning with zero on the left side of the module. If a match is found, the value of the StartColumn argument is set to the column on which the beginning character of the matching text is found.

EndLine   Required Long. The line on which to stop searching. If a match is found, the value of the EndLine argument is set to the line on which the ending character of the matching text is found.

EndColumn   Required Long. The column on which to stop searching. If a match is found, the value of the EndColumn argument is set to the column on which the beginning character of the matching text is found.

WholeWord   Optional Boolean. True results in a search for whole words only. The default is False.

MatchCase   Optional Boolean. True results in a search for words with case matching the Target argument. The default is False.

PatternSearch   Optional Boolean. True results in a search in which the Target argument may contain wildcard characters such as an asterisk (*) or a question mark (?). The default is False

Remarks
The Find method searches for the specified text string in a Module object. If the string is found, the Find method returns True.

To determine the position in the module at which the search text was found, pass empty variables to the Find method for the StartLine, StartColumn, EndLine, and EndColumn arguments. If a match is found, these arguments will contain the line number and column position at which the search text begins (StartLine, StartColumn) and ends (EndLine, EndColumn).

For example, if the search text is found on line 5, begins at column 10, and ends at column 20, the values of these arguments will be: StartLine = 5, StartColumn = 10, EndLine = 5, EndColumn = 20.

Example
The following function finds a specified string in a module and replaces the line that contains that string with a new specified line.


Function FindAndReplace(strModuleName As String, _

    strSearchText As String, _

    strNewText As String) As Boolean

    Dim mdl As Module

    Dim lngSLine As Long, lngSCol As Long

    Dim lngELine As Long, lngECol As Long

    Dim strLine As String, strNewLine As String

    Dim intChr As Integer, intBefore As Integer, _

        intAfter As Integer

    Dim strLeft As String, strRight As String



    ' Open module.

    DoCmd.OpenModule strModuleName

    ' Return reference to Module object.

    Set mdl = Modules(strModuleName)



    ' Search for string.

    If mdl.Find(strSearchText, lngSLine, lngSCol, lngELine, _

        lngECol) Then

        ' Store text of line containing string.

        strLine = mdl.Lines(lngSLine, Abs(lngELine - lngSLine) + 1)

        ' Determine length of line.

        intChr = Len(strLine)

        ' Determine number of characters preceding search text.

        intBefore = lngSCol - 1

        ' Determine number of characters following search text.

        intAfter = intChr - CInt(lngECol - 1)

        ' Store characters to left of search text.

        strLeft = Left$(strLine, intBefore)

        ' Store characters to right of search text.

        strRight = Right$(strLine, intAfter)

        ' Construct string with replacement text.

        strNewLine = strLeft & strNewText & strRight

        ' Replace original line.

        mdl.ReplaceLine lngSLine, strNewLine

        FindAndReplace = True

    Else

        MsgBox "Text not found."

        FindAndReplace = False

    End If



Exit_FindAndReplace:

    Exit Function



Error_FindAndReplace:



MsgBox Err & ": " & Err.Description

    FindAndReplace = False

    Resume Exit_FindAndReplace

End Function

		

Open in new window

0
 
LVL 4

Expert Comment

by:rowanscott
ID: 33700299
I have made a few suggested modifications to your code and attached it below.
Let me know if it works or not.

A few things that could be causing problems
Is the sheet you are looking up always the active sheet? Better to use its name.
Do the searched cells contain straight numbers not numbers stored as text or formulas returning numbers.
You must have the xlWhole paramater in because you no doubt want to match the whole value of the cell.

You can post your workbook or a scaled down version of it if you like and I can fix it for you

Best regards
Sub Sag_01()

Dim a As String
Dim b As String
Dim c As String
Dim d As Double
Dim m As Range ' you missed this declaration ##################

    a = UserForm1.TextBox1.Text    'Emplacement
    b = UserForm1.TextBox2.Text    'Code
    c = UserForm1.TextBox3.Text    'Quantité
    
    With ThisWorkbook.ActiveSheet.Range("E1:E50000") 'you may be better off naming the sheet  eg With Sheet1.range.........  ##################
    'use the sheets code name though, not the tab name which can be changed by the user
    
    'for a start. put this in
    'just in case non numeric value typed
    On Error Resume Next
        d = CDbl(b)
    On Error GoTo 0
    
    'next add this here   ##################
    'the values may not be necessary
        Set m = .Find(d, , xlValues, xlWhole)
        
        
        If Not m Is Nothing Then
            ActiveCell = m.Address
            ActiveCell.Offset(0, 3) = a
            ActiveCell.Offset(0, 1) = b
            ActiveCell.Offset(0, 1) = c
        Else
            MsgBox ("Allo SAG")
        End If
    End With
    
End Sub

Open in new window

0
 

Accepted Solution

by:
gahute earned 0 total points
ID: 33701565
Hi experts,

After a couple of hours reading and trying your suggestions, I think I found my error. In fact, my sub is located in my "PERSO.xls" file. So "ThisWorkbook" refers to "PERSO.xls", not the active workbook where I call the sub.

Anybody can confirm?

Thanks,

0
 
LVL 4

Expert Comment

by:rowanscott
ID: 33709425
Yes that is right. ThisWorkbook is the workbook containing the code. Activeworkbook can be the same or any other workbook making it not a common expression to use.

Same with ActiveSheet.  I always find it better to explicitely state which sheet. Only use active sheet if you mean whatever sheet is active.

Also. In the project explorer you will see something like Sheet1(SheetName)
Refering to the sheet as Workbook.Sheets("SheetName") only works until someone changes the name.
If you refer to it as Sheet1 it then nothing can go wrong.

Not sure if that helps.

Best regards
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now