Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

how do you convert

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » How do you convert a 'SEARCH array formula' into VBA code

How do you convert a 'SEARCH array formula' into VBA code

ResolvedVersion 2003
Edited on Fri 27 Jan 2012, 15:23

Tony has attended:
Excel VBA Advanced course

How do you convert a 'SEARCH array formula' into VBA code

Hi there,

I am having real difficulty in writing VBA code for the following Excel ARRAY formula

{=IF(SUM(--ISNUMBER(SEARCH("Sainsbury",D1:D100)))>0,"Sainsbury","(blank)")}

So, "Sainsbury" is returned if "Sainsbury" is found in the D1:D100 range, otherwise "(blank)" is returned. This will hopefully help me to choose both categories in Pivot Table - I have workd out the code for this part

Please can someone help me

Many thanks
Tony

RE: How do you convert a 'SEARCH array formula' into VBA code

Hi Tony

Try using Selection.FormulaArray.
This will add the {} around the formua and make it an array.

Selection.FormulaArray = _
"=IF(SUM(--ISNUMBER(SEARCH(""Sainsbury"",D1:D400)))>0,""Sainsbury"",""(blank)"")"

(lines 2 and 3 are really on 1 line)

There may be other ways but this seems to work.
Note you need to add double double quotes around the criteria.

Doug
Doug Dunn
Best STL

RE: How do you convert a 'SEARCH array formula' into VBA code

Hi Doug,

This is great and changes the cell as required.

I am now trying to change a pivot table to show either "Sainsbury" or "(blank)" depending on the oucome.

So I need to show "Sainbury" in the pivot table if my search finds "Sainbury"

eg by possibly using the following code
ActiveSheet.PivotTables("PivotTable3").PivotFields("Name"). _
CurrentPage = "Sainsbury"

or I need to show "(blank)" in the pivot table if my search does not find "Sainbury"

eg possibly using the following code
ActiveSheet.PivotTables("PivotTable3").PivotFields("Name"). _
CurrentPage = "(blank)"

I dont think I can substitute the pivot table codes above into the Selection.FormulaArray you supplied

Is there some other code which searches for "Sainsbury" in a list then changes the pivot table to either "Sainsbury" or "(blank)" depending on the outcome.

Thanks
Tony

RE: How do you convert a 'SEARCH array formula' into VBA code

Hi Tony

You can try an IF statement to test if the word Sainsbury was found from the Seach formula. Here's an example where the Data sheet contains only 2 columns Store and Location. Something like this

If Sheets("Data").Range("A1").Value = "Sainsbury" Then
ActiveSheet.PivotTables("PivotTable4").PivotFields("Store").CurrentPage = "Sainsbury"
End If

I've attached my example where the pivot table is added to the "Pivot" worksheets.

Hope that helps!

Doug

Attached files...

Tony array pivot.xls

 

Training courses

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

Change the Default Width of All Columns in Excel 2010

If you want to change the width of the columns in your Excel 2010 spreadsheet, making them either larger or smaller, here's how:

In the Cells group on the Home tab, click Format.

Hover over the section called Cell Size and a drop down list will appear, select Default Width from this list.

In the Standard Width dialog box, enter the size you want to set as the default width and click OK.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.