SUMPRODUCT/ISNUMBER/SEARCH for dynamic range on different worksheet - VBA

Buzzcut

New Member
Joined
Dec 16, 2014
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Hi Team,
I've been pulling my hair out for the whole morning trying to get this to work. You're my last hope!

Column S is free text field that can contain a country name anywhere.
I can get the formula to work fine when I hard-code values.

VBA Code:
wsMainData.Range("AJ2:AJ" & LastRow).Formula = "=SUMPRODUCT( -- ISNUMBER(SEARCH({""SINGAPORE"";""AUSTRALIA"";""VIET NAM"",S2)))>0"

This list is now dymanic and located on a sheet called Data in a separate workbook called Client from where the VBA is running.
I've tried to create the full range address using the name and parent.

VBA Code:
Dim y As String
y = "'[Client.xlsm]" & rngCountries.Parent.Name & "'!" & rngCountries.Address
wsMainData.Range("AJ2:AJ" & LastRow).Formula = "=SUMPRODUCT( -- ISNUMBER(SEARCH(y,S2)))>0"

y gives the correct value and it works when I manually replace in the sheet and copy down but the formula is just showing the text "y" after the VBA runs.

I've been trying to use the Evaluate and also Indirect to create the workbook/sheet/range to no avail.

VBA Code:
Evaluate("=SUMPRODUCT( -- ISNUMBER(SEARCH(y,S2)))>0")

It puts FALSE in every cell in the column. Any ideas how I can get this to work, I can loop through the full ranges but the SUMPRODUCT seems like a much quicker way?

Cheers...Buzz
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Try it like
VBA Code:
wsMainData.Range("AJ2:AJ" & LastRow).Formula = "=SUMPRODUCT( -- ISNUMBER(SEARCH(" & y & ",S2)))>0"
 
Upvote 1
Solution
Try it like
VBA Code:
wsMainData.Range("AJ2:AJ" & LastRow).Formula = "=SUMPRODUCT( -- ISNUMBER(SEARCH(" & y & ",S2)))>0"
omg...hangs head in shame.
A classic case of not seeing the wood for the trees!
Thanks..Buzz
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top