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.

 

[bschulze]

Thanks for the "UCase" tip, I have never used that function before...It works great....thanks again

 

[pgc01]

Hi bschulze

You can also tell vba that you want to perform a textual comparison.

If you want that for all the comparisons in the module just write the compare option at the top of the module before any procedure.

Option compare Text
...
...
If InStr(a.Value, "totals") Then
...

Another possibility is if you want this option to be valid for just a specific call of Instr. In this case use the optional arguments as I wrote in my post

If InStr(1, a.Value, "totals", vbTextCompare) Then

Any of the 2 will solve your problem.

HTH
PGC

 

[bschulze]

Thanks for all the good suggestions. I can not get this last part.

Set CurrentCell = Range("A2")
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Do While CurrentCell <> Range("A" & LastRow)

I want the operation to run until it gets to the last cell (LastRow) of the data. I would use isEmpty except there are Blank Rows that I need until this opertion runs. I cant figure it out....What happens when I run this now is it skips over the entire Do While process.

 

[Richard Schollar]

Hi

You're comparing values in the cells, not the cells themselves (with your code). Maybe you can use this:

Set CurrentCell = Range("A2") 
LastRow = Range("A" & Rows.Count).End(xlUp).Row 
Do While CurrentCell.Row < LastRow

Best regards

Richard

 

[bschulze]

works great....thanks for all the help everyone.

 

[just_jon]

If you're looking to get a bunch of cells here, better to loop through the target range vis Find, I think.

 

[bschulze]

I will check out the "Find" function. This would probally work out better for me since I am really only concerned with a couple thousand rows, but looking at upwards of 40,000. This would speed up the macro considerably...I was looking for a quick temporary solution, and I had some of this code handy....thanks for the suggestion.

 

[Richard Schollar]

If they all have the same value in them (these couple of thousand cells out of 40000) then the absolute quickest way bar none would be to use Autofilter. If you need help to use this feature, post back!

Richard