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.
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.
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.
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
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