If CurrentCell.Value *CONTAINS*

[bschulze]

I have been searching for this but my search criteria must be way off. I am trying to find out if it is possible use "*" for contains in this type of function. I have used "=" and "<>" however I now need to use contains and I am not able to figure this out. This is an example of what I am looking for:

            If CurrentCell.Value = "*Total*" Then
                   CopyRange.Select
                   Selection.Copy
                   ColumnCell.Select
                   ActiveSheet.Paste
            End If

The problem is that "Total" is buried in the cell and not buried in a consistent mannor. Any help would be great or if someone knows of a link. Thanks in advance.

 

[pgc01]

Hi bschulze

Try the Like operator or the function Instr:

If ActiveCell.Value Like "*total*" Then MsgBox "OK"

Or

If InStr(1, ActiveCell.Value, "total", vbTextCompare) > 0 Then MsgBox "OK"

Hope this helps
PGC

 

[Smitty]

How about InStr?

Like If InStr(CurrentCell.Value, "Total") Then

HTH,

Smitty

 

[bschulze]

I was suprised when this did not work, so I attempted a scaled down version (as if the scripts was not simple enough) and I still could not get this to work properly.

Set a = Range("A9")

If InStr(a.Value, "totals") Then
    Range("A1") = "TEST"
End If

Test #1: Cell A9 = "XtotalX"
---it did not find the "total" buried in the cell

Test #2: Cell A9 = "total"
---it found the match and installed "TEST" in cell A1


I am not very versed in using this function, am I missing something very basic? Any help would be great

 

[Peter_SSs]

bschulze

Try this:
<font face=Courier New>    <SPAN style="color:#00007F">Set</SPAN> a = Range("A9")
    
    <SPAN style="color:#00007F">If</SPAN> InStr(a, "total") <SPAN style="color:#00007F">Then</SPAN>
        Range("A1") = "TEST"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN></FONT>

 

[bschulze]

No luck on that solution either. Any other sugguestions?

 

[just_jon]

I was suprised when this did not work, so I attempted a scaled down version (as if the scripts was not simple enough) and I still could not get this to work properly.

Set a = Range("A9")

If InStr(a.Value, "totals") Then
    Range("A1") = "TEST"
End If

Test #1: Cell A9 = "XtotalX"
---it did not find the "total" buried in the cell

Test #2: Cell A9 = "total"
---it found the match and installed "TEST" in cell A1


I am not very versed in using this function, am I missing something very basic? Any help would be great

You are searching for totals

 

[bschulze]

that was a typo....The test cell (A9) was "XTotalsX" and "Totals".....my apologies....I have also retested this just to make sure and it does not work....Good catch though

 

[bschulze]

I think I may have figured out my issue. The "TOTAL" I am looking for in the cells are all CAPS. Once I changed the script to:

Set a = Range("A9")

If InStr(a.Value, "TOTALS") Then
    Range("A1") = "TEST"
End If

End Sub

It seems to work. Does anyone know of a way to ignore the format of the cell as far as CAPS. This is a spreadsheet I dont beleive I can count on remaining consistent.

 

[Richard Schollar]

Yep - just convert any value you pick up off the spreadsheet to uppercase:

Set a = Range("A9") 

If InStr(UCase(a.Value), "TOTALS") Then 
    Range("A1") = "TEST" 
End If 

End Sub

Then you'll always be comparing upper-case to upper-case.

Best regards

Richard