VBA SpecialCells Method

[jjlafond]

Is it possible to return a range containing a "specific text"?

Example: Return all cells containing *Pick this cell* in it?

 

[Rick Rothstein]

Is it possible to return a range containing a "specific text"?

Example: Return all cells containing *Pick this cell* in it?

Are the asterisks you placed around the text supposed to be "wildcards"? In other words, is the text the only thing in the cell or could it be embedded within other text in the cell?

 

[jjlafond]

The asterisks have no particular meaning, just a way to separate from the rest of the sentence.
I have several cells containing ONLY:

#1
#2
#1-4

I would like to select all cells containing #2.

 

[Rick Rothstein]

The asterisks have no particular meaning, just a way to separate from the rest of the sentence.
I have several cells containing ONLY:

#1
#2
#1-4

I would like to select all cells containing #2.

This function (which can only be called from other VB code not associated with a UDF), will return a range consisting of the cells whose sole content is the text passed into it as an argument...

Function RangeContaining(TextToFind As String) As Range
  ActiveSheet.UsedRange.Replace TextToFind, "#N/A", xlWhole
  Set RangeContaining = ActiveSheet.UsedRange.SpecialCells(xlConstants, xlErrors)
  RangeContaining.Value = TextToFind
End Function

Here is a simple macro that demonstrates the use of this function...

Sub Test()
  MsgBox RangeContaining("#2").Address(0, 0)
End Sub

 

[jjlafond]

You are a lifesaver. Works perfectly. I should be able to incorporate this into other areas of my code as well to save a lot of time.

 

[jjlafond]

Hopefully a little more help with this one too:

I am trying to create a line graph using the range I just created. This code comes from the macro recorder when selecting several cells one at a time. I would like to be able to create the graph using the defined range I just created (the new range contains only numbers defined by using an .offset on the sub above).


ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Range(''Mold X & R Template'!$C$24,'Mold X & R Template'!$E$24,'Mold X & R Template'!$G$24,'Mold X & R Template'!$I$24,'Mold X & R Template'!$K$24,'Mold X & R Template'!$M$24,'Mold X & R Template'!$O$24,'Mold X & R Template'!$Q$24,'Mold X & R Template'!$S$24")

I do not need the sheet name (Mold...Tempalte). All data comes from the same sheet. (Unless the format requires it to be there.)

 

[jjlafond]

I figured it out. Here's the code for anyone with the same issues:

ActiveChart.SetSourceData Source:=Target1Range