instr

  1. jmacleary

    See if a string contains any of a list of characters in VBA

    Good morning (or afternoon/evening). I have a vba application using forms and need to validate the user input to ensure none of a set of characters are being input. At the moment the set is )('@=`#~"!,. but this may grow. I was going to create a loop using instr, but wondered if there was a...
  2. BlakeSkate

    Make InStr with entire cell qualifier

    Is it possible to use InStr to where it will only return the position of a string if the phrase you are looking for is the entire cell. So say for example InStr(1, a(i, j), "err", 1) i want to find "err" in an array and not have it return "Sierra" only if the entire cell is "err" do i want it...
  3. B

    Comparing words in a cell to a fixed list of words

    Greetings, I have a spreadsheet with names and addresses. In the name column, I need to compare all the words to a fixed list of terms, such as "borrower", "homeowner", "LLC", etc. I will be highlighting the row to look at individually. So far, I can get the code to work, but with only one...
  4. A

    instr to get exact match

    I have this line of code, but I thought it was to get exact match. How can I get the exact match? If InStr(1, Cells(RowNum, 1).Value, "BA", vbTextCompare) > 0 Then
  5. Dr. Demento

    Finding the whole word from InStr

    Could someone point me in the direction of a function that once InStr finds a substring, the function returns the entire word? For example, fx_WholeWord("I went to the theater and someone must have turned on the heater", "heat") would return both "theater" and "heater" Thanks y'all.
  6. D

    VBA code to set 2 column Combobox values

    I'm abbreviating my descriptions to paint the picture. I have a userform that has 2 comboboxes on it. cmbPartNumber and cmbAccount cmbAccount is 2 columns populated from a worksheet For each row there is always a value in column 0 and sometimes a value in column 1 Click save on the userform...
  7. L

    Instr substrings

    Hi all, Is it possible to use multiple substrings with inStr? I want to test if the following characters are contained within mystring /, \, ?, %, *. Many Thanks
  8. S

    Dynamic Named Range as Array for AutoFilter not working

    Hey All! I've been searching the forums in vain and am hoping someone out there can help. I have a dynamic named range ("FilterCriteria") and am trying to use it as an array for autofilter. I'd like to see if any of the "FilterCriteria" values appear in each cell in Column H. H is a helper...
  9. B

    Find a string in a range

    I need the fastest way to look across a range of cells where each cell in the range contains a long string to see if any words in a list occurs in any of those cells. The first thing I did of course was to read the range of cells into an array, then I loop through my list (for each item in...
  10. B

    Performance question - string search across many cells

    I have about 10 cells in a row that contain strings. These strings can be null to paragraphs of text. I need to find out if a particular key word exists in any of the cells, for each row through hundreds of thousands of rows, then this gets nested in another loop - so I need this to be fast...
  11. M

    Solved: Problem with "Instr" when called with an Empty Thread

    If you call "Instr" with a null argument as in "Instr("Whatever","")" you always get back a 1. It should return a 0 indicateing that it didn't match anything. My solution:Function In_Str(Look_In, For_Text) ' Correcting "Instr" function. ' 7/3/18 Created. WML Prog = "In_Str"...
  12. M

    Add text based on date in another cell

    Hello, I was wondering if this was possible to do in vba code. I have a date in cell A15 and A16 ... 3/31/18 and 2/28/18 respectively. In the cells next to the date I need text that will pull the year and then some instr text or it can all be instr. I'm looking to type a file name like...
  13. F

    InStr Function

    Dim LastRow As LongDim erow As Long Dim i As Long Dim strComp LastRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row Sheet2.Select Range("A1").Select For i = 2 To LastRow strComp = Cells(i, 3) If InStr(1,strComp,"Fuel Consumed")= 1 Then erow = Sheet4.Cells(Rows.Count...
  14. S

    Worksheet Loop Macro Using InStr, maybe

    Greetings, I would like to build a simple macro to delete charts in the activeworkbook. I have the following written below: Dim x As Worksheet For Each x In ActiveWorkbook.Worksheets If InStr(1, ActiveWorkbook.Worksheet(x).Name, "Chart", 1) Then...
  15. S

    vlookup? instr? if?

    Hi again everyone, Q: what's the most efficient way to search for partial matches and populate results? I'm fairly beginner at vba but learning quickly. I have two very disorganized workbooks. Wb2 is where I will make all my edits. each workbook when exported has varying lines of code...
  16. S

    INSTR or IF Function - Picking out Specific text.strings in a Cell

    So I've run into a problem that I believe there is a solution for, however, I am not keen on how to implement the function. In the chart below you'll notice that all of the FALSE returns highlighted are actually TRUE. The code is as follows: Range("AC2").Formula =...
  17. X

    VBA Function which is somewhat a Reverse of CHOOSE

    We all know how CHOOSE Works eg. If I had month number and I wanted to convert them into Month name then I'd be using Choose ie. MonName=CHOOSE(Monno,"Jan", "Feb", "Mar", "Apr", "May" "Jun", "Jul", "Aug", "Sep", "Oct" "Nov" "Dec") But what my query is that if we were given month names and were...
  18. A

    Excel vba loop through list until

    Hello, I am trying to work on a way to automatically fill in a schedule for where someone should be. What I have so far is in my 1st sheet I have a table of cells I want to fill, across the columns i have dates, and down the rows I have the locations. In my 2nd sheet I have the dates of when...
  19. J

    InStr, vbTextCompare, Last.Collumn

    hi all, i am busy with an formula, for a quite big project. i use this code: V_End_Of_Table = ActiveSheet.UsedRange.Rows.Count Dim cell As Range For Each cell In Range("CA2:CA" & V_End_Of_Table) If InStr(1, cell.Value, "fcnswanlrtm", vbTextCompare) > 0 Then Range("CX" &...
  20. L

    Get rid of part of a string when the part to delete is at an unknown position

    I'm having a blank moment. Once a string variable has been set, I basically need to check it for certain substrings within it, and get rid of them, and join the string back up. It will mainly be getting rid of any occurrences where there is a double, concurrent occurrence of "%2B" in the...

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top