Searching title

[steve_bris]

Hi.

Firstly i would like to say how great the board is......I would have been stuck doing quit a few things without the search function and all the knowledge on the here

I am trying to copy and paste certain charts depending on what is in the chart title

This is part of a lot bigger code so I have just given the relevant line and a few surrounding ones

in this example I want to see if the title contains 111 with other words around it.......I also will want to search for specific words

Thanks for any help
Steve



intcrtNo =1

Sheets("Chart" & intCrtNo).Select
If ActiveChart.ChartTitle Like "*111*" Then <---------------------what is the best way or correct syntax for searching the chart title ?
intCrtNo = intCrtNo + 1
'paste it......
Else
'paste is somewhere else
intCrtNo = intCrtNo + 1
End if

 

[jimbrooking]

Use the Instr function

If Instr(1,ActiveChart.ChartTitle,"111") then

 

[steve_bris]

Awesome....thanks Jim.

Could you please explain what the first 1 in the line does ?

I assume Instr means "in string"

 

[steve_bris]

Sorry Jim...I am still getting an error.

Sheets("Chart" & intCrtNo).Select
If InStr(1, ActiveChart.ChartTitle, "111") Then

is it enought to make the correct sheet selected and then do the Instr command....or should I add another line in the middle to select the chart....and if so what should it be

Or maybe do I have to dim ChartTitle as a string or something ?

Thanks

 

[jimbrooking]

Suggest you check the Instr documentation in the online help.

The "1" says start with the first character in the string being searched.

The string being searched does need to be a string (per the online documentation). You may have to Dim strSomething as String and then set strSomething=ActiveChart.ChartTitle, or else try to use the CStr function to convert the ChartTitle to a string within the Instr call.

 

[Sergeant]

...or should I add another line in the middle to select the chart....and if so what should it be

Yeah, you can't refer to 'ActiveChart' unless there is an active chart. Instead of selecting things and then refering to them, you could just refer to them. If there's only one chart on each page, then try this...

If InStr(1, Sheets("Chart" & intCrtNo).ChartObjects(1).Name, "111") Then

(The '1' argument is which character to start the in string search with. Default is '1', so can be omitted. See the HELP for 'INSTR'.)

 

[steve_bris]

Thanks for the help guys....I used the below code and it works now...yay



Sheets("Chart" & intCrtNo).Select
ActiveChart.Select
strCrtName = ActiveChart.ChartTitle.Text
intCrtName = InStr(1, strCrtName, "AP3C")

If intCrtName = 0 Then
intCrtNo = intCrtNo + 1
Else
paste it etc