Free Microsoft Excel 2013 Quick Reference

Like operator

I am trying to write vba code so a user can enter a worksheet name using an
inputbox. The worksheets are named by the client name. e.g. "Smith, Adam" and
I would like the user to be able to put in the name "Smith" and have the
correct worksheet selected, or a range of worksheets if there are more than 1
"Smith". How can I write this. The code I have written so far is:

Private Sub Workbook_Open()
On Error GoTo ErrorHandler
Dim stclname As String
Dim stermes As String

Enterclname:

stclname = InputBox("Enter the Client Name")
Worksheets(stclname).Activate

Exit_Workbook_Open:
Exit Sub

ErrorHandler:

stermes = MsgBox("Check the spelling")
Resume Enterclname

End Sub

Thanks in advance


Post your answer or comment

comments powered by Disqus
Hello

Using the "Select Case", is there any way to use the like operator?

Ex:


	VB:
	
 ChartTitles() 
    Dim sht As Worksheet 
    Dim ChtTitle As String 
     
    For Each sht In ThisWorkbook.Worksheets 
        If TypeName(ActiveSheet) = "Chart" Then 
             
            Select Case ActiveChart.Name 
            Case 1 
                Like "Profit/Loss*" 'Error here, does not like "Like" or "Is Like"
                ChtTitle = "Profit/Loss" & " For " & sht.Name 
            Case 2 
                Like "Evaluation*" 
                ChtTitle = "Evaluation" & " For " & sht.Name 
            Case 3 
                Like "Expenses/Revenues*" 
                ChtTitle = "Expenses/Revenues" & " For " & sht.Name 
            End Select 
             
            With ActiveChart 
                .ChartTitle.Characters.Text = ChtTitle 
            End With 
        End If 
    Next sht 
End Sub 

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

Terry

Hi guys

The following like operator doesn't work.

I hope anyone can help me out.

If
ComboBox1.Value Like "Scanning *" Then
        msgbox "Scanning"
   End If
Regards

Dear all;

I have a front sheet were in a cell named txtrole one can enter a role (like
manager).
I want to search a column in a database on another sheet (Database) and find
all cells that contain the word manager.
So, manager qualifies, but lead manager also qualifies.
When a match is found, I want to copy the whole row to the first worksheet.

The code I developped -and which is not working- is written below.
Take into consideration that the code does start in the appropriate cell,
but that this part of the code is outside this sub.

Private Sub Check_02()
'Check Role
x2 = Worksheets("Front").Range("txtRole").Value
Do Until IsEmpty(ActiveCell)
If Application.Proper(ActiveCell.Offset(0, 4).Value) Like
Application.Proper(x2) Then
ActiveCell.EntireRow.Copy WF.Range("B20").Offset(i, -1)
i = i + 1
j = j + 1
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

---

What must I change so that I can check how many cells qualify.
Do I use the like operator right or do I have to do this differently.

Mark.

I am trying to find out if there is anyway to simulate the
functionality of the Reular Expression's "*" when using
the Like operator. specifically: in Regular expressions
the "*" refers to Multiple Occurences, so, [w]* mmeans
any multiple occurence of a word character,Equivalent to
[a-zA-Z0-9]. But with the Like operator "*" means "Any"
multiple characters, which includes the whole ASCII
character set. Is there a way to limit it to just
MULTIPLE characters that are just alpha-numeric.

Search term - "Mobile"
Result (takes into consideration) - "Mobile", "Mobilephone", "MobileCharger"

Question: How do I modify my code to only yield (take into consideration) the word 'Mobile' and NOT 'Mobilephone' or 'USBCharger' or 'AndroidPhone'. I know I have to use Regex, however not sure how to do it here. Can anyone guide me?


	VB:
	
 
Sub ColoringDeleting() 
    Dim WSHT As Worksheet, i As Long, Lastrow As Long 
    Set WSHT = ActiveSheet 
    i = 2 
    Lastrow = WSHT.Range("A" & wsh.Rows.Count).End(xlUp).Row 
    While i

I have got in A1: 66

Dim vWhat as variant
vWhat = 6

result = Cstr(Range("A1")) Like "*vWhat*"

Why result is FALSE???

Good day!

Whats wrong with this code.

Sub summarize_cbu()
Dim counter As Long
Dim counter2 As Long

    For counter = 2 To 787
            For counter2 = 6 To 296
                If Sheet1.Cells(counter, 1) Like Sheet2.Cells(counter2, 3) Then
                   Sheet1.Cells(counter, 2) = Sheet1.Cells(counter, 2) + Sheet2.Cells(counter2, 11)
                Else
                End If
            Next 
    Next
    
End Sub
Example value assignment.
If "Sample Value" Like "cbu-sample value" Then
     next line here must be executed..but it executed directly to else
else

end if
Thank you for helping!

So, I have a few lines in VBA that should when run sort through cells in the range and then based on whether it finds the partial strings in the Range delete the entire column. However, I can't get it to work without splitting it up into two For statements. Currently it only searches for the Second Case.

For Each Cell In Range("L1:AX1")
    Select Case True
    Case Cell Like "*XBA*"
    Cell.EntireColumn.Delete
    Case Cell Like "LBO*"
    Cell.EntireColumn.Delete
End Select
Next Cell
Is there a way two do this without splitting it up into two statements?

Hi,

I have a problem as detailed below and would appreciate any help on it.

Cell A1 contains the word "ISO"
Cell A2 contains the word "ISODEP"

I need a msgbox to pop up saying YES if it finds that cell A2 begins with the contents of cell A1.

I could manually enter this formula like this
-------
If range("A2")l.Value Like "ISO*" Then
MsgBox "YES"
End If
-------

But ISO could (and will) change to something else each day and I need it to be able to look at whatever someone has typed in cell A1.

Hope someone can help.

Thanks

John

I can't seem to find a way to use the "Like" operator with conditional formatting.

       
Range("F10:F30").Select
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
                                       Formula1:="=""TRG"""
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Font
            .Bold = True
            .Italic = True
            .TintAndShade = 0
        End With
Prior to formatting I would like to test the value of the cell if it contains "TRG" as the first three characters and then format "Bold = True and Italic = True.

All help is appreciated.

Hello, I could improve one of my Excel applications if I was able to display in bold the characters that caused a match in a "Like" expression.

For example, let us assume:

t1 = "Excel Forum"
t2 = "?x*l*"

We have (t1 Like t2) = True.
I would like to be able to display why the match occured.
In the example above, it would be enough if I could display something like "Excel Forum" where I used bold red characters to indicate the match.

Two task are needed to perform that:

1) finding the characters responsible for the task
2) displaying the string correctly

The second task might be difficult if the result would be returned by a UDF.
The first task is my main question.

I'm trying to validate the contents of a name text box on a userform. I used the following code:
i = 1
Do Until i > Len(txtSearchName)
    If Not Mid(txtSearchName, i, 1) Like "[A-Z,a-z,-,',., ]" Then
        MsgBox "Search name is invalid. You must enter valid search name to proceed." & vbCrLf &
"Please try again.", vbOKOnly
        txtSearchName.SetFocus
        NameBadFlag = True
        Exit Do
    End If
    i = i + 1
Loop
The above code allows all the characters, including apostrophe, period, and space, but not a hyphen. What gives? Thanks for any help you can lend.

I'm using the below code to get only those rows which contains 'Deploy' or 'Pre-prod' but it gives less rows than its supposed to do.

For b = 3 To 10000
    If Range("A" & Trim(Str(b))).Value = "" Then
         Exit For
         End If
       
    If ((Not Range("A" & Trim(Str(b))).Value Like "*Deploy*") And _
        (Not Range("A" & Trim(Str(b))).Value Like "*Pre-prod*")) Then
         Range("A" & Trim(Str(b)).Select
         Selection.Delete Shift:=xlUp
         b = b - 1
         End If
    Next


Hi all;
Wonder if anyone can offer a little help? I'm not much good at VBA, probably because I use it so rarely, but I've managed to almost get something working just right -but not quite!- We process a lot of peoples' names in our business, and clients submit thousands of them to us in all sorts of ways. Ideally they send us an Excel spreadsheet with columns for first, middle and last names.

But sometimes we get a spreadsheet with a single column in the format:
FIRSTNAME Lastname, for instance:
PETERSON John
JOHNSON Peter William
O'REILLY Emma
HANDFORD-WILLIAMS Jenny
McMURDO Phillip Frederick

I found and modified the following bit of code to process this into the format we need them in, i.e. all names in a single column in order of first name, middle names, then last name. It works just fine, except for a few things.
1. Does anyone know how to get an apostrophe chr(39) into the Like charlist on line 3? It fails if I add it in after the hyphen, and I need it to process names like O'REILLY.
2. It also doesn't like first names like MacSWEEN or McMURDO, as the lowercase letters throw a spanner in the works and end the macro too early. Is there a way of getting the Like operator to include these too?

Code below, many thanks folks!

Function getname(rng As Range) As String
Dim getcaps As String
For x = 1 To Len(rng)
    If Mid(rng, x, 1) Like "[ABCDEFGHIJKLMNOPQRSTUVWXYZ-]" And Not Mid(rng, x + 1, 1) Like "[a-z]" Then
        If Mid(rng, x + 1, 1) <> " " Then
        getcaps = getcaps & Mid(rng, x, 1)
            Else
            getcaps = getcaps & Mid(rng, x, 1) & " "
        End If
    End If
Next

getname = Trim(Mid(rng, Len(getcaps), Len(rng))) & " " & Trim(Mid(getcaps, 1, Len(getcaps)))

End Function


Ok, so I've looked and fiddle and bashed my head into the keyboard a few times but still no results. I want to open a series of workbooks that May start with different names but have one common word in the name. Here is what I have so far:


	VB:
	
 
Dim mybook As String 
Dim myfile As String 
Dim mst As Workbook 
 
Set mst = ActiveWorkbook 
mybook = ActiveWorkbook.Path 
 
For i = 1 To 6 
    myfile = ("*Stack " & i & ".xlsx") 
    Workbooks.Open Filename:=(mybook & myfile) 
    On Error Goto 0 
     
     
Next i 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Problem is it keeps giving me a 1004 error saying that C:Users*Stack1.xlsx cannot be found. Do I have to use an array or the Like operator(which I couldn't get to work as it kept returning Boolean results). Any thoughts?

I have one column in a worksheet that I want to split into 2 columns. If there is one "(" in the cell, I want to split at that point, but if there are two "(" 's, I want to split at the second occurrence.

I can use the Like operator to identify the cells, but is there a way to specify which delimiter in a sequence of identical delimiters to split at?

Here are templates (not actual data) of the two type of cells:

Name1 (ABC) (Name2, Name3)
Name4 (Name5, Name6)

For the first, I want to split on the second "(", before Name2, and for the second, I want to split on the first "(", before Name5.

Thanks.

For one of my spreadsheets I have a whole lot of named ranges, and I want to format several of them the same way. Can I group them together somehow so that I could have something like

	VB:
	
 Group1 
    blah blah 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I was eyeing the "Like" operator, but I'm not sure if that's the right one, or how I'd go about it. Any ideas?

Thanks!

Hi,

I want to open XL files that have a name similar to a list of names in a range of cells.

So far i have the following fragmented code i have pieced together.


	VB:
	
 Openfilesfromlist() 
    Dim i As Integer 
    Dim wbResults As Workbook 
     
    Dim BookName As String 
    Dim filename As String 
     
    On Error Resume Next 
     
    With Application.FileSearch 
        .NewSearch 
        .LookIn = "G:5762x-Reconciliation57621-LON ReconciliationBrokerageFX-MM" 
        .FileType = msoFileTypeExcelWorkbooks 
         
         'i want the file name to be determined by a list of names
        beginning In cell a1 
         'of the open workbook, not including paths- how can i combine this with the "IsLike" operator??
         'the filenames will not be exactly the same as the cell value's but will contain key words
         'such as "Garban"
         
        .filename = Cell.Value 
         
         
        If .Execute > 0 Then 'Workbooks in folder
            For i = 1 To .FoundFiles.Count 'Loop through all
                 'Open Workbook x and Set a Workbook variable to it
                Set wbResults = Workbooks.Open(.FoundFiles(i)) 
                 
                 
                 'i will be adding code here to work with each opened work book later
                 
            Next i 
        End If 
    End With 
     
    On Error Goto 0 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I was thinking of using the "Is Like" operator to determine which files should be opened but am unsure of the syntax.

Could someone please browse and help me out?

the above code will currently open all the files in the specified path above but will needs to incorporate a search based on the cell range.

Many thanks as always.

Neil

Hi Hi..... i have a problem, can any kind soul help me??
heehee thanxs!!

My problem is: i need to type vb macro codes in excel to search whether this 3 characters "SIN" is in the beginning of a string. Ermm..is it got to do with LIKE operator....heehee...if not ermm...then how :P??

I have a userform with a multiselect listbox the user seraches using a textbox. I am using the following code to do the search. the only problem is that the results appears at the bottom of the list and I want it to appear at the top.

Code:
 
Private Sub TextBox1_Change()
'the change event runs each time the user
'types into a text box
Dim s As String
Dim i As Integer
s = TextBox1.Text
'Note the use of the ListIndex property of the ListBox
'If the ListIndex is -1 means nothing selected
'If 0 means the first item selected
ListBox1.ListIndex = -1
If TextBox1.Text = "" Then  'nothing typed
    Exit Sub
End If
For i = 0 To ListBox1.ListCount - 1
    'use the LIKE operator to compare
    'convert both to Uppercase as well so case does not matter
    If UCase(ListBox1.List(i)) Like UCase(s & "*") Then
        ListBox1.ListIndex = i
        Exit Sub
    End If
Next
 
End Sub
Please help me.

Thanks in advance.

I am fairly new at this. I am trying to write a code that will allow me to search a multiselect listbox. The listbox has 4 column. The first column is Manager's names. I want to search this column by typing the name in a textbox. The code I have will find the name, but the first record that matches appear at the bottom of the listbox. This is the code i copied from another website:

Private Sub TextBox1_Change()
'the change event runs each time the user
'types into a text box
Dim s As String
Dim i As Integer
s = TextBox1.Text
'Note the use of the ListIndex property of the ListBox
'If the ListIndex is -1 means nothing selected
'If 0 means the first item selected
ListBox1.ListIndex = -1
If TextBox1.Text = "" Then 'nothing typed
Exit Sub
End If
For i = 0 To ListBox1.ListCount - 1
'use the LIKE operator to compare
'convert both to Uppercase as well so case does not matter
If UCase(ListBox1.List(i)) Like UCase(s & "*") Then
ListBox1.ListIndex = i
Exit Sub
End If
Next

End Sub

Also I want to be able to copy the selected items in the fourth column to another worksheet. Can someone help me?

Thanks,

clmack70

Hi..

How do I loop through the column names in Excel. I am aware of how to extract data from cells while looping through rows but I need to loop through column names e.g. A, B, C, D...

Next question is I have two strings which I have obtained from two different cells. Lets assume "you" and "thankyou" Is there a function in Excel that will let me check if the second string ends with the first string. Will the "Like" operator accomplish this?

Thanks in advance

Found what ive wasted my last 3 hours on.

It appears that macros dont like operating on hidden cells even if the information from the hidden cells is concatenated into visible cells.

Is there anyway to tag "PASTE VALUE" prior to a concatenation out of interest ?

Thanks everyone.

Hello Chaps.

I am currently having a really painful issue where my staff are manually typing individual emails from an inbox into a database.

I have mcgyvered a way to copy and paste the lines of text from these emails into coloumn A of an excel sheet and it will concatenate the required details from coloumn A into the cells I1 - V1 on sheet 1.

The problem is whenever I paste the next order into Coloumn A the details from I1-V1 get overwritten.

I need some way to copy the information from I1 - V1 on sheet 1 of my workbook to the next available empty row going down in sheet 2 under my coloumn headings.

This will save me having to manually insert a new row each time I paste.

If that is possible instead of manually typing each of these orders into a database all that will be required will be

1. Open the email copy the text
2. Paste into coloumn A on sheet one
3. Open next email and repeat.

Unless there is someone to export individual emails from outlook to excel this is the best I can come up with.

This will leave me with on excel sheet that I can import directly into the database.

Hope this make sense Im sorta at my wits end trying to figure it out. If anyone needs any further details please ask.

Cheers folks.

hello there,

i have to check whether a user has saved a particular workbook with the
..htm or .html extension. that is the easy bit.

the user has to save the workbook in a folder on the desktop.

i take the workbook's fullname (see sub below) and check to see if the
last 4 or 5 characters are .htm or .html respectively

please bear in mind that we are not intrested in the name of the
workbook. we just want him to give a name in the workbook and save it
as .htm or .html. see an example below.

C:Documents and SettingsnkantzelisDesktoptest*****.htm

that is the first bit.

now i want to check that if he saved it in the particular folder named
"test".

that is not a problem you might think. just take the 35 or so
characters from the left of the full name and see if test is included.

there is a problem though.

every user is going to log on on a different machine and have a
different user name

so the path will be like this

C:Documents and Settings********Desktoptest*****.htm

i can take the string using

workbookname= Application.Workbooks(1).fullname

so now workbookname contains something like this

C:Documents and Settings********Desktoptest*****.htm

is there any way that i can check that in this string there is a "test"
substring?

how can i do it using the Like operator?

that is mi question.

thanks a lot

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

Sub checkfilenameextension()

'On Error Resume Next

Set app = Application.Workbooks(1)

FullName = app.FullName

MsgBox FullName

MsgBox Right(FullName, 33)

If Right(FullName, 4) ".htm" _
And Right(FullName, 5) ".html" Then
ECDLANSWER = 2
MsgBox ("error")
Exit Sub
End If

MsgBox ("ok")

End Sub

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/


No luck finding an answer? You could always try Google.