How to find text within a range of values?

[RemyMaza]

Dave Peterson helped me a bunch with this code previously so cheers to
him. Now I need some more insight with this same code. My values
that I was searching for initially were hard coded but the code needs
to be a little more robust. What I have now is prefixes for these
values that I'd like to look past and get to the value that doesn't
change. i.e. ABC-1 TestCell could be ABC-2 TestCell or MC1 TestCell
could be I1 TestCell. Is there an easy way to search for the text
that doesn't change or will I have to create variable for each prefix
and reference them in? FYI, The prefixes do change in length, so some
of them in the range are 4 characters and some of them may only be 1
character.

Code Begins
******************************************************************************
Sub Button1_Click()
ActiveSheet.Unprotect
Dim myPath As String
Dim myRng As Range
Dim myCell As Range

myPath = "mypath"

With Worksheets("Sheet1")
Set myRng = .Range("E1", .Cells(.Rows.Count, "E").End(xlUp))
For Each myCell In myRng.Cells
Select Case LCase(myCell.Value)
'For Pic #1
Case LCase("TestCell") ' This is what I'd like to search
for within the cell value even though it's not the whole value
InsertPicture myPath & "\" & "1.jpg", _
myCell.Offset(0, -1), True, True
'End Pic #1, Start Pic #2
Case LCase("TestCell2") ' This is what I'd like to
search for within the cell value even though it's not the whole value
InsertPicture myPath & "\" & "2.jpg", _
myCell.Offset(0, -1), True, True
Case Else
End Select
Next myCell
End With
ActiveSheet.Protect
End Sub


Sub InsertPicture(PictureFileName As String, TargetCell As Range, _
CenterH As Boolean, CenterV As Boolean)
' inserts a picture at the top left position of TargetCell
' the picture can be centered horizontally and/or vertically
' http://www.exceltip.com/st/Insert_pictures_using_VBA_in_Microsoft_Excel/486.html
Dim p As Object, t As Double, l As Double, w As Double, h As Double
If TypeName(TargetCell.Parent) <> "Worksheet" Then Exit Sub
If Dir(PictureFileName) = "" Then Exit Sub
' import picture
Set p = TargetCell.Parent.Pictures.Insert(PictureFileName)
' determine positions
With TargetCell
t = .Top
l = .Left
End With
' position picture
With p
.Top = t
.Left = l
End With
Set p = Nothing
End Sub

*****************************************************************************************
End Code


Thanks for any suggestions!

Regards,
Matt

 

[RemyMaza]

Dave Peterson helped me a bunch with this code previously so cheers to
him.  Now I need some more insight with this same code.  My values
that I was searching for initially were hard coded but the code needs
to be a little more robust.  What I have now is prefixes for these
values that I'd like to look past and get to the value that doesn't
change. i.e.  ABC-1 TestCell could be ABC-2 TestCell or MC1 TestCell
could be I1 TestCell.  Is there an easy way to search for the text
that doesn't change or will I have to create variable for each prefix
and reference them in?  FYI, The prefixes do change in length, so some
of them in the range are 4 characters and some of them may only be 1
character.

Code Begins
***************************************************************************­***
Sub Button1_Click()
    ActiveSheet.Unprotect
    Dim myPath As String
    Dim myRng As Range
    Dim myCell As Range

    myPath = "mypath"

    With Worksheets("Sheet1")
        Set myRng = .Range("E1", .Cells(.Rows.Count, "E").End(xlUp))
        For Each myCell In myRng.Cells
            Select Case LCase(myCell.Value)
             'For Pic #1
            Case LCase("TestCell") ' This is what I'd like to search
for within the cell value even though it's not the whole value
                     InsertPicture myPath & "\" & "1..jpg", _
                     myCell.Offset(0, -1), True, True
             'End Pic #1, Start Pic #2
             Case LCase("TestCell2")  ' This is what I'd like to
search for within the cell value even though it's not the whole value
                     InsertPicture myPath & "\" & "2..jpg", _
                     myCell.Offset(0, -1), True, True
            Case Else
            End Select
        Next myCell
    End With
    ActiveSheet.Protect
End Sub

Sub InsertPicture(PictureFileName As String, TargetCell As Range, _
    CenterH As Boolean, CenterV As Boolean)
' inserts a picture at the top left position of TargetCell
' the picture can be centered horizontally and/or vertically
'http://www.exceltip.com/st/Insert_pictures_using_VBA_in_Microsoft_Exc...
Dim p As Object, t As Double, l As Double, w As Double, h As Double
    If TypeName(TargetCell.Parent) <> "Worksheet" Then Exit Sub
    If Dir(PictureFileName) = "" Then Exit Sub
    ' import picture
    Set p = TargetCell.Parent.Pictures.Insert(PictureFileName)
    ' determine positions
    With TargetCell
        t = .Top
        l = .Left
    End With
    ' position picture
    With p
        .Top = t
        .Left = l
    End With
    Set p = Nothing
End Sub

***************************************************************************­**************
End Code

Thanks for any suggestions!

Regards,
Matt

I've tried this but the code must not be written right because it
doesn't find anything and no pics are imported:

Case InStr(LCase(myCell.Value), LCase("TestCell"))
InsertPicture myPath & "\" & "2.jpg", _
myCell.Offset(0, -1), True, True

I think this code will work with a little bit of modification. Just
not sure what it is

Thanks,
Matt

 

[Dave Peterson]

Maybe it's time to drop the select/case structure and just do some
if/then/else's:

For Each myCell In myRng.Cells
if lcase(mycell.value) like lcase("abc-") then
InsertPicture myPath & "\" & "1.jpg", _
myCell.Offset(0, -1), True, True
else
if lcase(mycell.value) like lcase("MC1") then
InsertPicture myPath & "\" & "2.jpg", _
myCell.Offset(0, -1), True, True
end if
end if
Next myCell

 

[RemyMaza]

Maybe it's time to drop the select/case structure and just do some
if/then/else's:

        For Each myCell In myRng.Cells
            if lcase(mycell.value) like lcase("abc-") then
               InsertPicture myPath & "\" & "1.jpg", _
                     myCell.Offset(0, -1), True, True
            else
               if lcase(mycell.value) like lcase("MC1") then
                     InsertPicture myPath & "\" & "2..jpg", _
                     myCell.Offset(0, -1), True, True
               end if
            end if
        Next myCell













--

Dave Peterson- Hide quoted text -

- Show quoted text -

For the life of me, I can't seem to get this to work. This "Like"
only seems to work if the whole text is within the parentheses. I've
tried a few things like mycell.text and mycell.formula instead of
value but that doesn't seem to work either. This may help. The
values I'm looking through are referenced in from multiple pages with
an IF statement. Here's an example:

=IF('Sheet1'!X$31>=0.01,'Sheet1'!G$31,"")

But I don't think that has any relevance because the code has been
working previous with all of the values hard coded. I think I may
have to call multiple prefix variables, but I'd much rather search the
value instead. MUCH RATHER! I have checked the picture path and it's
good. I'm able to get it to work with the whole text in there but
that doesn't help me much LOL. I even found a way to do "Like" with
Case statements but that didn't work either. I'm not sure if "Like"
is going to work in my situation but I'm not really sure. Thanks for
your reply Dave and sorry it's taken me a while to post back but I
wanted to fully research it on my end to with your new code and see
why this isn't working. I'm now stuck in the mud. Any other ideas?

Thanks,
Matt

 

[RemyMaza]

For the life of me, I can't seem to get this to work.  This "Like"
only seems to work if the whole text is within the parentheses.  I've
tried a few things like mycell.text and mycell.formula instead of
value but that doesn't seem to work either.  This may help.  The
values I'm looking through are referenced in from multiple pages with
an IF statement.  Here's an example:

=IF('Sheet1'!X$31>=0.01,'Sheet1'!G$31,"")

But I don't think that has any relevance because the code has been
working previous with all of the values hard coded.  I think I may
have to call multiple prefix variables, but I'd much rather search the
value instead.  MUCH RATHER!  I have checked the picture path and it's
good.  I'm able to get it to work with the whole text in there but
that doesn't help me much LOL.  I even found a way to do "Like" with
Case statements but that didn't work either.  I'm not sure if "Like"
is going to work in my situation but I'm not really sure.  Thanks for
your reply Dave and sorry it's taken me a while to post back but I
wanted to fully research it on my end to with your new code and see
why this isn't working.  I'm now stuck in the mud.  Any other ideas?

Thanks,
Matt- Hide quoted text -

- Show quoted text -

I've got it to work. I was looking for the wrong thing. What I
needed to do was put in "*Text*" in the string I was searching. Since
I was trying to get past the prefix, this is what I had to do to
accomplish that. Dave thanks again for your insight. I'm off to take
some VBA courses. This stuff is too cool. One giant puzzle waiting
to be broken. Thanks a million!

Regards,
Matt

 

[RemyMaza]

I've got it to work.  I was looking for the wrong thing.  What I
needed to do was put in "*Text*" in the string I was searching.  Since
I was trying to get past the prefix, this is what I had to do to
accomplish that.  Dave thanks again for your insight.  I'm off to take
some VBA courses.  This stuff is too cool.  One giant puzzle waiting
to be broken.  Thanks a million!

Regards,
Matt- Hide quoted text -

- Show quoted text -

I was also able to keep Case Statements with this code:


Select Case True
Case LCase(myCell.Value) Like LCase("*15A DUPLEX REC
OUTLET")
InsertPicture myPath & "\" & "2.jpg", _
myCell.Offset(0, -1), True, True
Case LCase(myCell.Value) Like LCase("*15A GFI REC
OUTLET")
InsertPicture myPath & "\" & "4.jpg", _
myCell.Offset(0, -1), True, True

Matt