98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
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
Resolved · Urgent Priority · Version 2003
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
Mon 30 Jan 2012, 11:48Trainer Doug said...
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
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
Tue 31 Jan 2012, 10:10Trainer Doug said...
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 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. |
Excel tip:Counting Non Number Cells (Text)If you try to use the COUNT FUNCTION =COUNT(Cell range)with a range of cells with numbers and or containing text fields you wil find that that the text cells will be excluded from the the count. If you want to include them try the the COUNTA FUNCTION =COUNTA(Cell range). This counts both text and number cell values. |
