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
Tony array pivot.xls
