Free Microsoft Excel 2013 Quick Reference

Searching text in excel Results

Hey guys!

Got a question regarding the Range.Find function. I'm using it to search within the customer workbook, but while testing, I found something weird. Lemme give you an example :

This is my search code (please note, French version of Excel 2007, thus Feuil1 = Sheet1, and last row is 1048576) :

	VB:
	
strName = Feuil1.Range("D13") 
OpenFile ("Clients") 
With Worksheets(1).Range("A1:A1048576") 
    Set SRange = .Find(strName, LookIn:=xlValues) 
    If Not SRange Is Nothing Then 
        CloseFile ("Clients") 
        FillCustomerInfo (strName) 
        Application.ScreenUpdating = True 
        Exit Sub 
    Else 
        Feuil1.Range("D15:D17") = " " 
        AjoutClient.Show 
    End If 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
In my search range I have a test value in column A that's "RRRRR", let's say it searches for the value "RRRR" (one less R), it returns as found on "RRRRR". (I know, the example sucks)

Within the function FillCustomerInfo, wich is passed the value of the strName, is uses this code to populate de invoice :


	VB:
	
) 
    OpenFile ("Clients") 
    Feuil1.Range("D15") = WorksheetFunction.VLookup(strName, Range("A2:D1048576"), 2, False) 
    Feuil1.Range("D16") = WorksheetFunction.VLookup(strName, Range("A2:D1048576"), 3, False) 
    Feuil1.Range("D17") = WorksheetFunction.VLookup(strName, Range("A2:D1048576"), 4, False) 
    CloseFile ("Clients") 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
wich returns an error code for "RRRR" is not in the worksheet. I'm sure I'm just forgetting an option within the original find...

Help please! Thanx!

W@rrior

Hi,

Ok this is really wierd, i am trying to do a search and i came across this. In the excel file attached you will see 2 column. Now if you take copy and paste "sterling rd" which is standing alone into the crtl F search field. It wont find it.

I have played with the format (made both general), i have CLEAN, Proper the text and tried that didnt work either.

I dont know what else to try. I am working with access, with a much larger list, however the linking to excel wont work and i know there is some difference font or format that i am missing.

Thank you for your help.

I need an excel function that will search the entire active workbook for a specific text string and display the value (a number) of the cell next to it. I have a marco on a worksheet, that, based on the data in that worksheet, inserts a specific worksheet from a different workbooks. each newly inserted worksheet has many fields, but only one cell with the text "TOTAL" in it but the cell location of the text varies across different worksheets. Is there a nested function that would search the entire workbook for "TOTAL" and display the value of the cell to the right of it.
many thanks

Hello, I am pretty experienced with Excel, but new to macros and VBA. I have spent a few days writing a macro to promt the user to make various selections and then insert the data into specific locations in specific sheets based on the user input.

I am now attempting to write another macro that will promt the user to select some serch criteria, retrieve specfic rows of data from a particular sheet (based on user input) and populate a list on a "menu" or "home" sheet. I have used the .find function do do this once. The problem is that in some cases there will be multiple instances on a particular sheet. I haven't figured out a way to make it create a list with all of the applicable instances.

For now I would be happy to search a specific column for text (user input) and copy each row of that sheet containing the specified text to the list on the "menu" sheet.

I'm afraid this might be unclear, and I would be glad to try to explain more. Would anyone be able to suggest some direction? Thanks very much. I've already found answers to a few other questions from this site.

-Pete

Hello Everyone,

I am searching a report in excel that lists the sales people and then the products that they sold. The sales people's "Rep# 123" are found in columns A and B and can vary frequently due to the way our database output into excel. I have been able to find all of the sales people using the find method and searching for their "Rep#". Now I need to find all of the products they sold under text "CFP". This text always falls under column B. I have been using the following code to find "CFP" in column B but I have been having a hard time in forcing it to search only between the first Rep found and the second.

The problem lies within the countif method in bold. The rFndRep1 and rFindRep2 can throw an address in column A or in column B. Is there a way force these address to look in column B always? I tried offset(0,1) but this screws up an address that is already in column B.

Any help much appreciated.


	VB:
	
 
    Set rFndProd = Sheet2.Cells().Find( _ 
    What:="CFP", LookIn:=xlValues, LookAt:=xlPart, _ 
    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) 
     
    str1 = Split(WorksheetFunction.Trim(rFndRep1.Value), " ") 
    str2 = Split(WorksheetFunction.Trim(rFndProd.Offset(0, -1).Value), " ") 
     
    Set rPrint = Sheet1.Cells(Rows.Count, "A").End(xlUp).Offset(1) 
    rPrint(1, "A").Value = str1(1) 
    rPrint(1, "B").Value = str1(2) 
    rPrint(1, "C").Value = str1(3) 
     
    If 2 = Len(WorksheetFunction.Trim(rFndProd.Offset(0, -1).Value)) - _ 
    Len(WorksheetFunction.Substitute(rFndProd.Offset(0, -1).Value, " ", "")) + 1 Then 
         
        rPrint(1, "D").Value = str2(0) 
        rPrint(1, "F").Value = str2(1) 
         
    Else 
        rPrint(1, "D").Value = str2(0) 
        rPrint(1, "E").Value = str2(1) 
        rPrint(1, "F").Value = str2(2) 
    End If 
    Set rFndProd = Sheet2.Cells.FindNext() 
    iCount = iCount + 1 
Loop While iCount  WorksheetFunction.CountIf([b]Range(rFndRep1, rFndRep2)[/b], "LTC") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi all,

-> In Excel Cell L4 to L9999, I would like to have Excel have that cell Hyperlinked of a Google search of the contents which were entered in that cell. (or, if that same cell can't be Hyperlinked, then a neighboring cell is OK)

-> So, for example, if cell L4 contained the word: "testing",
I would like to be able to click on that text in the cell to open a browser and take me to: "http://www.google.com/search?q=testing"

...I hope this can be done without writing a macro (maybe by a formula instead?) BUT, if it's the only way of doing it, then yes, please, in that case, I would LOVE to know how to write/enter the macro.

Thanks very much,
CG

Hello to everybody,

Any help on this one would be great.

I've got a problem that's causing me a headache. I have a document with 5 columns and lots and lots of rows.

I need to do a search cell by cell (in the first column) for any cells that contain the text "changeType". When a cell is found, I need excel to insert two entire new rows below the current cell and insert the text "modify" into the two new rows (in the first column). I also need to insert, in the first new row, but on column 5, the text "IN" and on the second new row, still on column 5, the text "OUT". The problem is that these two rows need to be inserted after the second time the text "changeType" appears, like the example below:

eg.

x x x x x
x x x x x
x x x x x
changeType x x x x
changeType x x x x
x x x x x
x x x x x

Becomes:

x x x x x
x x x x x
x x x x x
changeType x x x x
changeType x x x x
modify IN
modify OUT
x x x x x
x x x x x

I dont use excel much so i'm pretty novice.

Thanks for any help you can offer

Loriza

Hi there

I'm hoping someone can help me out here, I have searched and searched on google for asisatnce and within Excel's own help info but to no avail. Maybe i am using the wrong terminology, who knows!?!?!?

What I want to be able to do is to have a text value in a cell which will be the same as a named range and can be called from a formula in another cell.

For example, I have three named ranges: JAN, FEB and MAR. Instead of having a formula which might read:

=VLOOKUP("bill", JAN, 1, 1)

I would like to have in cell A1 the text value "JAN" so that the formula can read:

=VLOOKUP("bill", A1, 1, 1)

Then I can change which named range is used in the formula but changing the value in cell A1.

When I try to do this, the formula just looks up the value as if cell A1 was the range rather than taking the value from A1 as the named range.

Can anyone help me around this?

Thanks

right, heres the problem.

i have an excel spreadsheet which has a long list of product codes referenced with descriptions in the cell next to them. what i want to do is to search the down the column of product codesto find the correct value one (a mixture of text and numerical characters). then take the corresoponding description and have it displayed in a message box. the description itself is a couple of sentences, possibly up to 30 words of text.

Two Excel files open
Text in column A for each, not necessarily matching perfectly between files.
Data in column B in one file needs to be copied and pasted into the second file, once a match is found,
Need a search and 'close' match procedure.
Once done, we can invoke a dialogue box that asks the user, "is this the match you're looking for". If yes, copy and paste the data from column B from the first file to the second file. If no, go back and look for the next 'close match', looping through the second file column A. If not found, do manually.
Only problem is to find the 'close match'.
Example:
Text in file #1 is "The black dog jumped over the fence"
Text in file #2 is "The dog jumped over the fence"
If this is a good enough match for the data in Column B to be used, we want this to be identified, giving the user an opportunity to accept or reject.
Any ideas?
Thanks in advance
Kevin

Hi
I am quite new to VBA scripting. I have a spreadsheet that imports a text file every hour from an FTP server. A new txt file is created in the FTP directory and is named by date and time eg: filename1907071200.Then the data is sorted onto worksheets. I am trying to automate this with a macro and have been able (thanks to this forum) to use a bat file and ftp.exe to log on and download a file with a specific name. The problem is in the FTP folder there are thousands of txt files (1 for every hour of every day since 2002) and instead of manually selecting the newest file I need to get the macro to search for the newest file and ONLY import that file. I have so far tried application.filesearch pointing to the ftp folder however excel just locks up.

Any ideas will be much appreciated

Thanks

Hi!

I am reading in and parsing a delimited file via VBA and then searching the data for the relevant maximum value. My problem is that the respective maximum value is not returning the decimal, but rather the whole integer. For reference, the problem code is (CLng was an attempt to rectify):


	VB:
	
(Application.WorksheetFunction.Max(myRange)) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Here is the entire sub:


	VB:
	
 
Option Explicit 
 
Sub ResultFileImport() 
     
     'Reads a text file into
     'July, 2007
     
     
     'Read results text file and send to worksheet for parse
    Dim NumberofResultFiles As Integer 
    Dim ResultStartColumn As Integer 
    Dim NumberofResultTags As Integer 
    Dim ResultTags() As String 
    Dim ResultValues() As Long 
    Dim TypeofOptimal As String 
    Dim SendFile As String 
    Dim Pararef As String 
     
    NumberofResultFiles = 3 
    ResultStartColumn = 14 
    Pararef = "Parameters" 
     
     'First read respective results file name
    Dim i As Integer 
    For i = 0 To NumberofResultFiles - 1 
         
         'Read respective result tags for parse
        NumberofResultTags = ActiveWorkbook.Sheets(Pararef).Cells(2, ResultStartColumn + (5 * i) + 1) 
        Redim ResultTags(NumberofResultTags - 1) 
        Redim ResultValues(NumberofResultTags - 1) 
        Dim ii As Integer 
        For ii = 0 To NumberofResultTags - 1 
            ResultTags(ii) = Sheets(Pararef).Cells(ii + 5, ResultStartColumn + (5 * i)) 
        Next 
         
         'Read result type
        TypeofOptimal = Sheets(Pararef).Cells(2, ResultStartColumn + (5 * i) + 3) 
         
         'Import entire text file to sheet for parse
        SendFile = Sheets(Pararef).Cells(3, ResultStartColumn + (5 * i) + 1) 
        Call CSVImport(SendFile) 
         
         'Search for respective values dependent upon type
        If TypeofOptimal = "Max" Then 
             
            Dim myRange As Range 
             'Search for appropriate row, exits on first match
            Set myRange = ActiveWorkbook.ActiveSheet.Range("A1", Range("A1").End(xlToRight)) 
            Dim c As Object 
             
            For Each c In myRange.Cells 
                 
                Dim DataElement As Variant 
                DataElement = c.Value 
                 
                If DataElement Like ResultTags(i) & "*" Then 
                     'Changes the range to the repsective column, takes into consideration peculiar csv import (blank cells)
                    Dim tempRange1 As Range 
                    Dim tempRange2 As Range 
                     'Looks for blank cells - PECULIAR to this data set
                    Set tempRange1 = Columns(c.Column).Find("") 
                    Set tempRange2 = Columns(c.Column).FindNext(tempRange1) 
                    Dim r1, r2, c1, c2 As Integer 
                    r1 = tempRange1.Row 
                    r2 = tempRange2.Row 
                    c1 = tempRange1.Column 
                    c2 = tempRange2.Column 
                    Set myRange = Range(ActiveSheet.Cells(r1 - 1, c1), ActiveSheet.Cells(r2 - 1, c2)) 
                     'Assign the max value to the results array
                    ResultValues(i) = CLng(Application.WorksheetFunction.Max(myRange)) 
                    Exit For 
                     
                End If 
                 
                If c.Column = myRange.Count Then 
                     
                    Dim ErrorMSG As Integer 
                    ErrorMSG = MsgBox("Error in results import. Check parameters.", vbOKOnly) 
                    End 
                     
                End If 
                 
            Next 
             
        End If 
         
    Next 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The value is a decimal in the worksheet. Just in case , here is the parse sub I am using (created using Macro record and modified):


	VB:
	
) 
     '
     'Creates a new worksheet and imports comma delimited file to an Excel worksheet
     'July, 2007
     '
     
     'Inserts new worksheet at end
    ActiveWorkbook.Sheets.Add After:=Sheets(Sheets.Count) 
     
     'FilePathandName = "D:Thesis_HMSFlow Values.txt"
    FilePathandName = "TEXT;" & FilePathandName 
     
    With ActiveSheet.QueryTables.Add(Connection:= _ 
        FilePathandName, Destination:=Range("A1")) 
        .Name = "Flow Values" 
        .FieldNames = True 
        .RowNumbers = False 
        .FillAdjacentFormulas = False 
        .PreserveFormatting = True 
        .RefreshOnFileOpen = False 
        .RefreshStyle = xlInsertDeleteCells 
        .SavePassword = False 
        .SaveData = True 
        .AdjustColumnWidth = True 
        .RefreshPeriod = 0 
        .TextFilePromptOnRefresh = False 
        .TextFilePlatform = 437 
        .TextFileStartRow = 1 
        .TextFileParseType = xlDelimited 
        .TextFileTextQualifier = xlTextQualifierDoubleQuote 
        .TextFileConsecutiveDelimiter = False 
        .TextFileTabDelimiter = False 
        .TextFileSemicolonDelimiter = False 
        .TextFileCommaDelimiter = True 
        .TextFileSpaceDelimiter = False 
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) 
        .TextFileTrailingMinusNumbers = True 
        .Refresh BackgroundQuery:=False 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello VBA MVP and experts,

I am expecting the output strings under column header "Old Material" where the desirable specific strings of text are retrieved from column "Remark". Column "Old Material" can be in any column, not necessarily in col EO, but column "Remark" is always one column to the right.

There are only 7 Nested-If allowed and thus my formua below wont do much justice. I have 10,000 + rows to work on each week and it has been a terrible nightmare in my work - stress, late hours, sleepless nights...

VB code surely will do the trick. I am hoping someone will be kind to help me on this. I shall be more than grateful.

Thanks a thousand times!!
Bill

I'm using Excel 2003, Windows XP

IF(LEFT(EO9,8)="replaced",MID(EO9,SEARCH("replaced ",EO9)+10,LEN(EO9)),IF(LEFT(EO9,8)="replaces",MID(EO9,SEARCH("replaces ",EO9)+10,LEN(EO9)),
IF(LEFT(EO9,8)="replace ",MID(EO9,SEARCH("replace ",EO9)+9,LEN(EO9)),IF(LEFT(EO9,5)="takes",MID(EO9,SEARCH("Takes the place of ",EO9)+20,LEN(EO9)),
IF(LEFT(EO9,4)="take",MID(EO9,SEARCH("Take the place of ",EO9)+19,LEN(EO9)),IF(LEFT(EO9,5)="takes",MID(EO9,SEARCH("Takes the place ",EO9)+17,LEN(EO9)),""))))))

I found this question from 2004 but it has expired so I could not quote or reply to it. It can be found at http://www.ozgrid.com/forum/showthread.php?t=21192

This was the question:
I'm looking for some code that i can attach to a command bar which allows the user to browse and search for any text file on their C:Drive and then import it. I think i can work out the import using the data import wizard and macro recorder - hopefully.

This was the suggested answer from Dave Hawley:

	VB:
	
 DoIt() 
    Application.Dialogs(xlDialogImportTextFile).Show 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The original person said they can work out the import using the wizard, but my users are not tech savvy and therefore I was wondering if I could automate the text file showing up in the excel sheet (named 'Import') after selecting the text file (instead of going to the wizard)

I have attached a sample text file for an example (first row being a header)
Thanks in advance

Ok, I have more of a general question, rather then a specific bug.

Short version:
How can I add multiple cells (ranges) to a listbox, and when the user clicks one, it will activate that cell in excell. I thought about have the list box for the text, and a corresponding array that holds the actual range or address, but I cant get it working.

------------------------------

Long version:
I have a search feature that, when you type a name, it searches through a column and finds the name. Now if there are multiple matches to your string, I want it to add the full name to a listbox, and have the user pick one from the list to activate.

So basically, if I search for the name "john" but theres multiple people with that name ("john doe", "john smith", & "john mazz"), it would add the three to a list box, I would pick one from the list, and it would activate that cell.

I have everything done except that last part. I have it adding multiple matches to the list box, but I cant figure out an easy way to activate the correct cell when double clicking the item in the list. I could have it search again for the selected full name, but there is likely to be a case where one person is entered twice.

----------------

Any help would be great! Thanks.

Hi,

I receive data from different sources and one column is usually in upper case, but I prefer to use proper (title) case. The problem is that the data frequently contains text strings that should remain in upper case or should combine upper and lower case letters. Examples follow:

Data received:
COROLLA AE90, AE92
COROLLA SX, GTI

Data should look like:
Corolla AE90, AE92
Corolla SX, GTi

Note that 'GTi' is upper case 'GT' and lower case 'i'.

I've set up a spreadsheet 'CaseConverter.xls' as a look-up, with a range ('SpecialCase' - A2:A65536) featuring names, codes and acronyms represented correctly. Is it possible to use a macro to run Excel's global replace to search for upper case strings in one spreadsheet and replace with the correct strings from the other spreadsheet? The macro would have to loop through the entire 'SpecialCase' range and look in the supplied spreadsheet for versions of the data in the special case range without being case-specific. The supplied spreadsheet (see attached for example data) is called 'Application.xls'. Column B is what I'm trying to reach.

Any assistance will be gratefully received.

Ken

I have built a simple search tool. Users input text data into a few controls one one page and press a button, then the code activates the sheet full of data and brings up a dialog box with their results. Once the dialog box is in front of them, clicking on a result in the dialog box selects the appropriate row on the worksheet (since the text in the box is truncated). Once they're done they can close the dialog and work the data directly. It was working perfectly, but after being placed on the intranet to be accessed the code that selects the line in the worksheet is no longer working properly.

After finding some information, I have updated the code a bit (now I'm trying to select a range using cells(), rather than trying to use EntireRow. The code will now run, but only once. A user can select a line once, but if they try to click on another line this error occurs:

Runtime error '1004':
Activate method of Range class failed.

Here is the code than runs when the dialog box is clicked:


	VB:
	
 lstResults_Click() 
    Dim xlBook As Excel.Workbook 
    Dim xlSheeet As Excel.Worksheet 
     
    Set xlBook = ThisWorkbook 
    Set xlSheet = xlBook.Worksheets("Schedule") 
     
    If (lstResults.ListIndex  -1) Then 
        iRow = lstResults.List(lstResults.ListIndex, 0) 
        xlSheet.Activate 
        xlSheet.Range(xlSheet.Cells(iRow, 1), xlSheet(Cells(iRow, 8)).Activate 
    End If 
     
    Set xlSheet = Nothing 
    Set xlBook = Nothing 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I'd appreciate any suggestions you might have.

Jeff Plummer

hi guys,

i am importing a large number of text files from a folder into an excel worksheet (into column A) and found the following code from searching the forum.


	VB:
	
 Import_Text_Files() 
     
    Const PATH = "C:Documents and SettingsSilent BazMy DocumentsElectronic Gift Card WorkTestTextFolder" 
     
    Dim My_Filenumber As Integer 
    Dim My_File As String 
    Dim My_Data As String 
     
    My_File = Trim(Dir(PATH)) 
     
    If My_File = "" Then 
        MsgBox "No Files found matching " & PATH & My_Extension 
        Exit Sub 
    End If 
     
    Application.ScreenUpdating = False 
    With ActiveSheet 
        While My_File  "" 
            My_Filenumber = FreeFile 
            Open PATH & My_File For Input As #My_Filenumber 
            While Not EOF(My_Filenumber) 
                Line Input #My_Filenumber, My_Data 
                .Range("A" & .Range("A65536").End(xlUp).Row + 1) = My_Data 
            Wend 
            Close My_Filenumber 
             'Kill PATH & My_File   'remove comment when macro works!.
            My_File = Dir 
        Wend 
    End With 
    Application.ScreenUpdating = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
the code works fine on a few test files, but in each text file there is a header and footer row that is not required. is there a way of adapting the code to remove the first and last row of data from each file as it is being imported?

i also need a way of preventing a file being imported if there is no room left for it on the sheet, as the contents of all the text files total c.350,000 rows of data. is this possible?

thanks in advance
bazzason

Hi everybody.

I've browsed through several examples I've found here and even tried playing with a bit of code to solve this problem, but failed miserably. Any help that can be provided would be greatly appreciated.

I've got a macro that reads a text file into a single column with range "A1:A10000" in worksheet "Source" that lives within the workbook containing this macro. I'd like to be able to search that worksheet for the cell that contains a particular text string, say " Excel", and have the macro return the address of the cell that contains it. I've tried using the .Find property but I only seem to be getting the value that I'm searching for instead of the cell address. Clues?

Much appreciated.

Hi Guys,

This is wrekcing my head as I'm sure it can be done with an Array formula and I cant seem to get it sorted -

Say I have 5 Rows of text starting in A2
"A quick word"
"A quick WORD"
"A"
"word"
"WORD"

I need an excel formula to say "False", "False", "False", "False", "TRUE".

All conditions to be met for TRUE are -
Word must in uppercase
Uppercase Word must be longer than 1 character
Sentence (i.e. cell) can not contain a space.

This is the path I've gone down, but almost certainly incorrect -

=IF(FIND(CHAR(64+COLUMN($A$1:$Z1)),$A$2,1),TRUE,FALSE)

[CTRL+SHIFT+ENTER]

Any ideas / pointers welcome.

Thanks,
Ger