UDF using application.worksheetfunction and structured reference in a table

[Lucky_Irishman]

Error in UDF using application.worksheetfunction and structured reference in a table.

Compile Error:
Expected: Expression

The object is to replace the complex formula in the "Carrier" column with a UDF.
I'm having trouble with the syntax of this part. I don't need help replicating the entire formula in VBA.

Thanks in advance for your help.

Function Carrier()
   ' UDF using application.worksheetfunction and structured reference in a table
   ' Compile Error:
   ' Expected: Expression
   
   Dim strInput
   Dim strOutput
   
   strInput = "usps"
   strOutput = "USPS"

   Carrier = Application.WorksheetFunction.IFERROR(IF(FIND(strInput, [@[Tracking  Link]])<>0,strOutput),"")
  
End Function

Orders_test.xlsm
	A	B	C
1	Item	Tracking Link	Carrier
2	Item 1	https://www.fedex.com/apps/fedextrack/	Fedex
3	Item 2	https://www.amazon.com/progress-tracker/	Amazon
4	Item 3	https://wwwapps.ups.com/etracking/	UPS
5	Item 4	https://www.pochta.ru/	Russian Post
6	Item 5	http://www.dhl-usa.com/	DHL
7	Item 6	http://link.order.homedepot.com/	Home Depot Express Delivery
8	Item 7	https://www.sf-international.com/	SF-International
9	Item 8	https://www.yuntrack.com/	YunExpress
10	Item 9	https://tools.usps.com/	USPS
Order Tracking

Cell Formulas
Range	Formula
C2:C10	C2	=IF([@[Tracking Link]]="", "",IFERROR(IF(FIND("homedepot", [@[Tracking Link]])<>0,"Home Depot Express Delivery" ),IFERROR(IF(FIND("fedex", [@[Tracking Link]])<>0,"Fedex"),IFERROR(IF(FIND("usps", [@[Tracking Link]])<>0,"USPS" ), IFERROR(IF(FIND("amazon", [@[Tracking Link]])<>0,"Amazon" ),IFERROR( IF( FIND("ups", [@[Tracking Link]])<>0, "UPS"),IFERROR( IF(FIND("canadapost", [@[Tracking Link]])<>0,"Canada Post"),IFERROR(IF(FIND("dhl", [@[Tracking Link]])<>0,"DHL"),IFERROR(IF(FIND("sf-international", [@[Tracking Link]])<>0,"SF-International"),IFERROR(IF(FIND("Pickup", [@[Tracking Link]])<>0,"Pickup" ),IFERROR( IF(FIND("yuntrack", [@[Tracking Link]])<>0,"YunExpress"),IFERROR(IF(FIND("pochta", [@[Tracking Link]])<>0, "Russian Post"),"" ))))))))))))

 

[Micron]

I suspect your issue is that you're passing variables as literals. Usually variables require concatenation when creating formulas or expressions.
Perhaps

Dim strFormula As String
strFormula = "Application.WorksheetFunction.IFERROR(IF(FIND( " & strInput & ",[@[Tracking Link]])<>0," & strOutput & ")0,' ' )"
Carrier = strFormula

I've shown 2 single quotes with spaces (red) to show it isn't a double quote. You'd remove the space in between. However, I don't know if the formula will accept them, or if you'll have to use double quotes and escape them in vba. That might need to be 0,"""")""" or something similar. Also, if the input and output are strings, they'll need delimiters also.

 

[BSALV]

ups.xlsx
	A	B	C	D	E	F
1	Item	Tracking Link	Carrier		Search	Carrier
2	Item 1	https://www.fedex.com/apps/fedextrack/	Fedex		homedepot	Home Depot Express Delivery
3	Item 2	https://www.amazon.com/progress-tracker/	Amazon		fedex	Fedex
4	Item 3	https://wwwapps.ups.com/etracking/	UPS		usps	USPS
5	Item 4	https://www.pochta.ru/	Russian Post		amazon	Amazon
6	Item 5	http://www.dhl-usa.com/	DHL		ups	UPS
7	Item 6	http://link.order.homedepot.com/	Home Depot Express Delivery		canadapost	Canada Post
8	Item 7	https://www.sf-international.com/	SF-International		dhl	DHL
9	Item 8	https://www.yuntrack.com/	YunExpress		sf-international	SF-International
10	Item 9	https://tools.usps.com/	USPS		Pickup	Pickup
11		https://tools.ussps.com/	-		yuntrack	YunExpress
12					pochta	Russian Post
Blad1

Cell Formulas
Range	Formula
C2:C11	C2	=IFERROR(INDEX(Tabel1[Carrier],MAX(IF(ISNUMBER(SEARCH(Tabel1[Search],B2)),ROW(Tabel1[Carrier])-ROW(Tabel1[[#Headers],[Carrier]]),-1))),"-")

 

[Micron]

The object is to replace the complex formula in the "Carrier" column with a UDF.

Did I misinterpret that to mean that a formula was not desired?
If not, my code has an extra 0 in it somehow, so that would need to be removed (as per below). Also, if code is what is required and the single delimiters are an issue, I have figured out the syntax for double quotes:

strFormula = "Application.WorksheetFunction.IFERROR(IF(FIND(""" & strInput & """"
strFormula = strFormula & ",[@[Tracking Link]])<>0,""" & strOutput & """),"""")"

Output is
Application.WorksheetFunction.IFERROR(IF(FIND("usps",[@[Tracking Link]])<>0,"USPS"),"")
I also note that there are 2 spaces between tracking and link and I removed one - perhaps not correct to do that.

 

[Lucky_Irishman]

I suspect your issue is that you're passing variables as literals. Usually variables require concatenation when creating formulas or expressions.
Perhaps

Dim strFormula As String
strFormula = "Application.WorksheetFunction.IFERROR(IF(FIND( " & strInput & ",[@[Tracking Link]])<>0," & strOutput & ")0,' ' )"
Carrier = strFormula

I've shown 2 single quotes with spaces (red) to show it isn't a double quote. You'd remove the space in between. However, I don't know if the formula will accept them, or if you'll have to use double quotes and escape them in vba. That might need to be 0,"""")""" or something similar. Also, if the input and output are strings, they'll need delimiters also.

Did I misinterpret that to mean that a formula was not desired?
If not, my code has an extra 0 in it somehow, so that would need to be removed (as per below). Also, if code is what is required and the single delimiters are an issue, I have figured out the syntax for double quotes:

strFormula = "Application.WorksheetFunction.IFERROR(IF(FIND(""" & strInput & """"
strFormula = strFormula & ",[@[Tracking Link]])<>0,""" & strOutput & """),"""")"

Output is
Application.WorksheetFunction.IFERROR(IF(FIND("usps",[@[Tracking Link]])<>0,"USPS"),"")
I also note that there are 2 spaces between tracking and link and I removed one - perhaps not correct to do that.

You are correct, there was an extra space. Glad you noticed. The output is now just text. I must be missing something.

Item	Tracking Link	Carrier	Carrier_Micron
Item 1	https://www.fedex.com/apps/fedextrack/	Fedex	Application.WorksheetFunction.IFERROR(IF(FIND("usps",[@[Tracking Link]])<>0,"USPS"),"")
Item 2	https://www.amazon.com/progress-tracker/	Amazon	Application.WorksheetFunction.IFERROR(IF(FIND("usps",[@[Tracking Link]])<>0,"USPS"),"")
Item 3	https://wwwapps.ups.com/etracking/	UPS	Application.WorksheetFunction.IFERROR(IF(FIND("usps",[@[Tracking Link]])<>0,"USPS"),"")
Item 4	https://www.pochta.ru/	Russian Post	Application.WorksheetFunction.IFERROR(IF(FIND("usps",[@[Tracking Link]])<>0,"USPS"),"")
Item 5	http://www.dhl-usa.com/	DHL	Application.WorksheetFunction.IFERROR(IF(FIND("usps",[@[Tracking Link]])<>0,"USPS"),"")
Item 6	http://link.order.homedepot.com/	Home Depot Express Delivery	Application.WorksheetFunction.IFERROR(IF(FIND("usps",[@[Tracking Link]])<>0,"USPS"),"")
Item 7	https://www.sf-international.com/	SF-International	Application.WorksheetFunction.IFERROR(IF(FIND("usps",[@[Tracking Link]])<>0,"USPS"),"")
Item 8	https://www.yuntrack.com/	YunExpress	Application.WorksheetFunction.IFERROR(IF(FIND("usps",[@[Tracking Link]])<>0,"USPS"),"")
Item 9	https://tools.usps.com/	USPS	Application.WorksheetFunction.IFERROR(IF(FIND("usps",[@[Tracking Link]])<>0,"USPS"),"")

 

[Lucky_Irishman]

ups.xlsx
	A	B	C	D	E	F
1	Item	Tracking Link	Carrier		Search	Carrier
2	Item 1	https://www.fedex.com/apps/fedextrack/	Fedex		homedepot	Home Depot Express Delivery
3	Item 2	https://www.amazon.com/progress-tracker/	Amazon		fedex	Fedex
4	Item 3	https://wwwapps.ups.com/etracking/	UPS		usps	USPS
5	Item 4	https://www.pochta.ru/	Russian Post		amazon	Amazon
6	Item 5	http://www.dhl-usa.com/	DHL		ups	UPS
7	Item 6	http://link.order.homedepot.com/	Home Depot Express Delivery		canadapost	Canada Post
8	Item 7	https://www.sf-international.com/	SF-International		dhl	DHL
9	Item 8	https://www.yuntrack.com/	YunExpress		sf-international	SF-International
10	Item 9	https://tools.usps.com/	USPS		Pickup	Pickup
11		https://tools.ussps.com/	-		yuntrack	YunExpress
12					pochta	Russian Post
Blad1

Cell Formulas
Range	Formula
C2:C11	C2	=IFERROR(INDEX(Tabel1[Carrier],MAX(IF(ISNUMBER(SEARCH(Tabel1[Search],B2)),ROW(Tabel1[Carrier])-ROW(Tabel1[[#Headers],[Carrier]]),-1))),"-")

Even though this is not what I was looking for, it works perfectly, will be easy to modify, and will likely perform better than a UDF on a large table.

 

[Micron]

Your function was written to only return a string. If you wanted an actual formula, you'd need to begin with equals sign?
I would agree with using the formula if it works. Note also that Find is case sensitive so you'd have that constraint as well.

 

[Lucky_Irishman]

Your function was written to only return a string. If you wanted an actual formula, you'd need to begin with equals sign?
I would agree with using the formula if it works. Note also that Find is case sensitive so you'd have that constraint as well.

The formula BSALV posted is better than the solution I was asking for.

I can see why the confusion now that I look back at my post. I meant I didn't need the whole formula that would find each carrier. Just the correct syntax for the part I posted. I thought that would be simpler. Maybe not so much. Thank you for your help.

 

[Peter_SSs]

Welcome to the MrExcel board!

It looks like you may be dealing with two tables? If so, or in any case, would this shorter structure work just as well for you?
For me, the left table is Table1 and the right table is Table2

22 02 20.xlsm
	A	B	C	D	E	F
1	Item	Tracking Link	Carrier		Search	Carrier
2	Item 1	https://www.fedex.com/apps/fedextrack/	Fedex		homedepot	Home Depot Express Delivery
3	Item 2	https://www.amazon.com/progress-tracker/	Amazon		fedex	Fedex
4	Item 3	https://wwwapps.ups.com/etracking/	UPS		usps	USPS
5	Item 4	https://www.pochta.ru/	Russian Post		amazon	Amazon
6	Item 5	http://www.dhl-usa.com/	DHL		ups	UPS
7	Item 6	http://link.order.homedepot.com/	Home Depot Express Delivery		canadapost	Canada Post
8	Item 7	https://www.sf-international.com/	SF-International		dhl	DHL
9	Item 8	https://www.yuntrack.com/	YunExpress		sf-international	SF-International
10	Item 9	https://tools.usps.com/	USPS		Pickup	Pickup
11	Item 10	https://tools.ussps.com/	-		yuntrack	YunExpress
12					pochta	Russian Post
13
14
Lookup

Cell Formulas
Range	Formula
C2:C11	C2	=IFNA(LOOKUP(9^9,SEARCH(Table2[Search],[@[Tracking Link]]),Table2[Carrier]),"-")

If it is a layout as in post #3 then replace [@[Tracking Link]] with B2

=IFNA(LOOKUP(9^9,SEARCH(Table2[Search],B2),Table2[Carrier]),"-")

 

[Lucky_Irishman]

=IFNA(LOOKUP(9^9,SEARCH(Table2[Search],[@[Tracking Link]]),Table2[Carrier]),"-")

Thanks, this one looked good, but when I applied it to the real sheet, there were incorrect lookups. The problem is that real links can have 100+ characters, which may contain more than one valid result from the search table. For example a long Amazon link may include "UPS" or "DHL" somewhere in it. For some reason, the other solution did give any false results. I'm not sure why.