If CurrentCell.Value *CONTAINS*

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289
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:

Code:
            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.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi bschulze

Try the Like operator or the function Instr:


Code:
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

Legend
Joined
May 15, 2003
Messages
29,536
How about InStr?

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

HTH,

Smitty
 

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289
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.

Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,424
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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>
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

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.

Code:
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

Active Member
Joined
Jun 2, 2005
Messages
289
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

Active Member
Joined
Jun 2, 2005
Messages
289
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:

Code:
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Yep - just convert any value you pick up off the spreadsheet to uppercase:

Code:
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
 

Forum statistics

Threads
1,136,354
Messages
5,675,296
Members
419,559
Latest member
BraytonM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top