Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » How do you convert a 'SEARCH array formula' into VBA code
|
|
|
The UK's most regular 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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
How do you convert a 'SEARCH array formula' into VBA code
Resolved · Urgent Priority · Version 2003
Reply Fri 27 Jan 2012, 15:20
Edited on Fri 27 Jan 2012, 15:23Delegate Tony said...
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
For upcoming training course dates see: Pricing & availability
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
Reply Mon 30 Jan 2012, 12:33Delegate Tony said...
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
|