none
How to refer to a variable value contained anywhere in a cell RRS feed

  • Question

  • I'm sure my subject title / question isn't worded properly and makes no sense.

    I want to tell VBA that if a Cell contains a variable (strName), anywhere within the cell, then mark a 'point' to another variable (lngWork).. Basically, if my associates names show up in the text of various cells then they worked that particular issue and I need to track their progress.

    I also want to avoid having to deal with upper and lower case completely and the name can appear anywhere within the cell.

    Here are several different attempts at what I'm trying to use but it's not working. 

    If ActiveSheet.ListObject(1).Range("A1").Value = "*" & strName & "*" Then lngWork = 1
    
    If ActiveSheet.ListObject(1).Range("A1").Value = "=*" & strName & "*" Then lngWork = 1
    
    If ActiveSheet.ListObject(1).Range("A1").Value = strName Then lngWork = 1
    

    It's late and I'm tired but I may try something like this tomorrow:

    ("=*" & strName & "*") ... or ... "*strName*" ...  Something has to work :(


    I use the same "=*" & XYZ & "*" syntax when I set the Criteria1: field of an AutoFilter and it works perfectly. Upper or lower case, finds the value anywhere within the range, but I guess the same doesn't work for .Value =...

    I also have about 30 different values that I check for strName, so I need to keep this as simple and compact as possible. Any suggestions are welcome and appreciated.


    I'm new, please help. Begging for forgiveness in advance.


    • Edited by JMStumpf Thursday, November 17, 2011 3:35 AM
    Thursday, November 17, 2011 3:32 AM

Answers

  • You don't describe your worksheet setup, but ListObject would generally be used with tables, and the Range property would be defined in terms of row and column (NOT like the Range property of the worksheet object).

    If you just want to check a particular cell on the active sheet to see if it contains a particular string, case-insensitive, you can use:

    If InStr(1, Range("A1"), strName, vbTextCompare) > 0 Then lngWork = 1

    If you want to search a range for strName, then I would use the Find method:

    If Not Range("A1:C10").Find(what:=strName, LookIn:=xlValues, lookat:=xlPart) Is Nothing _
        Then lngWork = 1

    If you want to search a single cell in a table, then it may be appropriate to use the listobject.range property, but you need to define the range in terms of row and column.  And if you need to search an entire table, you can use the dataobjectrange property to return the actual address, and then use the find method.


    Ron
    Thursday, November 17, 2011 4:13 AM
  • Hello again JMStumpf,

    The code to address specific cells within a Table (List Object) is a little different to what one would expect. Range is used the way Cells is used on a worksheet.

    When comparing values using wildcards, Like is used in lieu of equals.

    To ignore case, use LCase or UCase to convert both sides of the comparison to either lower or upper case. Note that this does not convert the values in the cells to lower or upper case; the conversion is only done temporarily in the line of code for the purpose of comparison.

    Sub Test_1()
    Dim strName As String
    Dim lngWork As Long

    strName = "bbb"   'For testing only

    'In the following line Range(2,1) is Row 2, Column 1 of the Table.
    If LCase(ActiveSheet.ListObjects(1).Range(2, 1).Value) _
            Like "*" & LCase(strName) & "*" Then lngWork = 1

    MsgBox lngWork

    End Sub

    As another suggestion, if you use object variables (declared at the top of the sub) the VBA's intellisence kicks in when you use the object varible. Each time you enter a dot or bracket etc you get a dropdown list of options. You will find this very helpful when trying to find the correct syntax. See example below and test by entering the If line of code yourself. (You can just enter it under the existing line so you have the code to copy from.)

    From other posts that I have answered for you it appears that you are working extensively with Tables and I thought that you might find this helpful.

    Sub Test_2()
    Dim objList As ListObject
    Dim strName As String
    Dim lngWork As Long

    strName = "bbb"   'For testing only

    Set objList = ActiveSheet.ListObjects(1)

    If LCase(objList.Range(2, 1).Value) _
            Like "*" & LCase(strName) & "*" Then lngWork = 1

    MsgBox lngWork


    Regards, OssieMac
    Thursday, November 17, 2011 7:11 AM
  • I'm sure my subject title / question isn't worded properly and makes no sense.

    I want to tell VBA that if a Cell contains a variable (strName), anywhere within the cell, then mark a 'point' to another variable (lngWork).. Basically, if my associates names show up in the text of various cells then they worked that particular issue and I need to track their progress.

    I also want to avoid having to deal with upper and lower case completely and the name can appear anywhere within the cell.

    Here are several different attempts at what I'm trying to use but it's not working. 

    If ActiveSheet.ListObject(1).Range("A1").Value = "*" & strName & "*" Then lngWork = 1
    
    If ActiveSheet.ListObject(1).Range("A1").Value = "=*" & strName & "*" Then lngWork = 1
    
    If ActiveSheet.ListObject(1).Range("A1").Value = strName Then lngWork = 1
    

    It's late and I'm tired but I may try something like this tomorrow:

    ("=*" & strName & "*") ... or ... "*strName*" ...  Something has to work :(


    I use the same "=*" & XYZ & "*" syntax when I set the Criteria1: field of an AutoFilter and it works perfectly. Upper or lower case, finds the value anywhere within the range, but I guess the same doesn't work for .Value =...

    I also have about 30 different values that I check for strName, so I need to keep this as simple and compact as possible. Any suggestions are welcome and appreciated.


    I'm new, please help. Begging for forgiveness in advance.



    Just a thought! Why can't you use Cells.Find and check it?

     

    Sub FindWithinCell()
    
    Dim oRange As Range
    Dim oFnd As Range
    Dim strName As String
    Set oRange = ActiveSheet.Range("A1")
    
    strName = "sam"
    
    'lookat:=xlPart will search for anywhere in the cell
    Set oFnd = oRange.Find(what:=strName, LookIn:=xlValues, lookat:=xlPart)
    
    If Not oFnd Is Nothing Then
        lngWork = 1
    End If
    
    
    End Sub
    
    

    Cheers

    Shasur

     


    http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com
    Thursday, November 17, 2011 9:54 AM

  • Hi Jason

    By default Find is not case sensitive. You can, however, explicitly make the search to be case-insensitive by setting the Matchcase to False

     

    Set oFnd = oRange.Find(what:=strName, LookIn:=xlValues, lookat:=xlPart, <strong>MatchCase:=False</strong>)
    

    Cheers

    Shasur


    http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com
    Thursday, November 17, 2011 12:45 PM

All replies

  • You don't describe your worksheet setup, but ListObject would generally be used with tables, and the Range property would be defined in terms of row and column (NOT like the Range property of the worksheet object).

    If you just want to check a particular cell on the active sheet to see if it contains a particular string, case-insensitive, you can use:

    If InStr(1, Range("A1"), strName, vbTextCompare) > 0 Then lngWork = 1

    If you want to search a range for strName, then I would use the Find method:

    If Not Range("A1:C10").Find(what:=strName, LookIn:=xlValues, lookat:=xlPart) Is Nothing _
        Then lngWork = 1

    If you want to search a single cell in a table, then it may be appropriate to use the listobject.range property, but you need to define the range in terms of row and column.  And if you need to search an entire table, you can use the dataobjectrange property to return the actual address, and then use the find method.


    Ron
    Thursday, November 17, 2011 4:13 AM
  • Hello again JMStumpf,

    The code to address specific cells within a Table (List Object) is a little different to what one would expect. Range is used the way Cells is used on a worksheet.

    When comparing values using wildcards, Like is used in lieu of equals.

    To ignore case, use LCase or UCase to convert both sides of the comparison to either lower or upper case. Note that this does not convert the values in the cells to lower or upper case; the conversion is only done temporarily in the line of code for the purpose of comparison.

    Sub Test_1()
    Dim strName As String
    Dim lngWork As Long

    strName = "bbb"   'For testing only

    'In the following line Range(2,1) is Row 2, Column 1 of the Table.
    If LCase(ActiveSheet.ListObjects(1).Range(2, 1).Value) _
            Like "*" & LCase(strName) & "*" Then lngWork = 1

    MsgBox lngWork

    End Sub

    As another suggestion, if you use object variables (declared at the top of the sub) the VBA's intellisence kicks in when you use the object varible. Each time you enter a dot or bracket etc you get a dropdown list of options. You will find this very helpful when trying to find the correct syntax. See example below and test by entering the If line of code yourself. (You can just enter it under the existing line so you have the code to copy from.)

    From other posts that I have answered for you it appears that you are working extensively with Tables and I thought that you might find this helpful.

    Sub Test_2()
    Dim objList As ListObject
    Dim strName As String
    Dim lngWork As Long

    strName = "bbb"   'For testing only

    Set objList = ActiveSheet.ListObjects(1)

    If LCase(objList.Range(2, 1).Value) _
            Like "*" & LCase(strName) & "*" Then lngWork = 1

    MsgBox lngWork


    Regards, OssieMac
    Thursday, November 17, 2011 7:11 AM
  • I'm sure my subject title / question isn't worded properly and makes no sense.

    I want to tell VBA that if a Cell contains a variable (strName), anywhere within the cell, then mark a 'point' to another variable (lngWork).. Basically, if my associates names show up in the text of various cells then they worked that particular issue and I need to track their progress.

    I also want to avoid having to deal with upper and lower case completely and the name can appear anywhere within the cell.

    Here are several different attempts at what I'm trying to use but it's not working. 

    If ActiveSheet.ListObject(1).Range("A1").Value = "*" & strName & "*" Then lngWork = 1
    
    If ActiveSheet.ListObject(1).Range("A1").Value = "=*" & strName & "*" Then lngWork = 1
    
    If ActiveSheet.ListObject(1).Range("A1").Value = strName Then lngWork = 1
    

    It's late and I'm tired but I may try something like this tomorrow:

    ("=*" & strName & "*") ... or ... "*strName*" ...  Something has to work :(


    I use the same "=*" & XYZ & "*" syntax when I set the Criteria1: field of an AutoFilter and it works perfectly. Upper or lower case, finds the value anywhere within the range, but I guess the same doesn't work for .Value =...

    I also have about 30 different values that I check for strName, so I need to keep this as simple and compact as possible. Any suggestions are welcome and appreciated.


    I'm new, please help. Begging for forgiveness in advance.



    Just a thought! Why can't you use Cells.Find and check it?

     

    Sub FindWithinCell()
    
    Dim oRange As Range
    Dim oFnd As Range
    Dim strName As String
    Set oRange = ActiveSheet.Range("A1")
    
    strName = "sam"
    
    'lookat:=xlPart will search for anywhere in the cell
    Set oFnd = oRange.Find(what:=strName, LookIn:=xlValues, lookat:=xlPart)
    
    If Not oFnd Is Nothing Then
        lngWork = 1
    End If
    
    
    End Sub
    
    

    Cheers

    Shasur

     


    http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com
    Thursday, November 17, 2011 9:54 AM
  • OssieMac I can always count on you to come through with the best answer ;) Ron thank you for your suggestions as well, I had found some documentation on InStr but wasn't quite sure how to make it fit my needs. Shasur, and everyone please correct me if I'm wrong, my understanding is that Find is case specific or case sensitive, so I wanted to avoid that situation. 

    Thanks to everyone, yet again, for showing me the correct way to use some of these commands... Can't wait till my courses start next year so I can actually Learn the fundamentals of VBA instead of just 'winging it'. I will let you know what I go with and which works best for me.

    Jason


    I'm new, please help. Begging for forgiveness in advance.
    Thursday, November 17, 2011 12:35 PM

  • Hi Jason

    By default Find is not case sensitive. You can, however, explicitly make the search to be case-insensitive by setting the Matchcase to False

     

    Set oFnd = oRange.Find(what:=strName, LookIn:=xlValues, lookat:=xlPart, <strong>MatchCase:=False</strong>)
    

    Cheers

    Shasur


    http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com
    Thursday, November 17, 2011 12:45 PM
  • Oh, I did not know that, been reading too many other forums with old information I guess. Thanks for the clarification and tip! Still working on recoding some of the other suggestions this morning but now I can also add Find to my tool belt!
    I'm new, please help. Begging for forgiveness in advance.
    Thursday, November 17, 2011 1:17 PM