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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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
 
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

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
Back
Top