?
Solved

Use Excel VBA .find function

Posted on 2010-09-16
5
Medium Priority
?
577 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

765 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