Solved

Use Excel VBA .find function

Posted on 2010-09-16
5
556 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

756 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