UDF using application.worksheetfunction and structured reference in a table

L

Lucky_Irishman

New Member
Joined
Feb 19, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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.


VBA Code: 
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
ABC
1ItemTracking LinkCarrier
2Item 1https://www.fedex.com/apps/fedextrack/Fedex
3Item 2https://www.amazon.com/progress-tracker/Amazon
4Item 3https://wwwapps.ups.com/etracking/UPS
5Item 4https://www.pochta.ru/Russian Post
6Item 5http://www.dhl-usa.com/DHL
7Item 6http://link.order.homedepot.com/Home Depot Express Delivery
8Item 7https://www.sf-international.com/SF-International
9Item 8https://www.yuntrack.com/YunExpress
10Item 9https://tools.usps.com/USPS
Order Tracking
Cell Formulas
RangeFormula
C2:C10C2=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"),"" ))))))))))))
 
Sort by date Sort by votes
Lucky_Irishman said:
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.
Click to expand...
I did wonder about those short strings and whether they may turn up elsewhere.

Could you give an example of a string where my suggestion gave the incorrect result and the other suggestion did not?

Also, are you able to post your actual lookup table? That is, your equivalent of my right hand table.
 
Upvote 0

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Peter_SSs said:
I did wonder about those short strings and whether they may turn up elsewhere.

Could you give an example of a string where my suggestion gave the incorrect result and the other suggestion did not?

Also, are you able to post your actual lookup table? That is, your equivalent of my right hand table.
Click to expand...

Actually, Now I've I've found that Micron's solution can fail as well.
Either idea will work if the lookup table uses the domain name ("amazon.com" instead of just "amazon"). That solved the problem.

Thanks for your help

Examples of all proposed soulutions below:
Orders_test posted to Mr Excel Forum.xlsm
ABCDEFG
1ItemTracking LinkCarrier _ OriginalCarrier_MicronCarrier_BSALVCarrier_Peter_SSs Using Domain Name LookupCarrier _Peter_SSs Using Domain Name Lookup
2Item 1https://www.fedex.com/apps/fedextrack/FedexApplication.WorksheetFunction.IFERROR(IF(FIND("usps",[@[Tracking Link]])<>0,"USPS"),"")FedexFedexFedex
3Item 2https://www.amazon.com/progress-tracker/AmazonApplication.WorksheetFunction.IFERROR(IF(FIND("usps",[@[Tracking Link]])<>0,"USPS"),"")AmazonAmazonAmazon
4Item 3https://wwwapps.ups.com/etracking/UPSApplication.WorksheetFunction.IFERROR(IF(FIND("usps",[@[Tracking Link]])<>0,"USPS"),"")UPSUPSUPS
5Item 4https://www.pochta.ru/Russian PostApplication.WorksheetFunction.IFERROR(IF(FIND("usps",[@[Tracking Link]])<>0,"USPS"),"")Russian PostRussian PostRussian Post
6Item 5http://www.dhl-usa.com/DHLApplication.WorksheetFunction.IFERROR(IF(FIND("usps",[@[Tracking Link]])<>0,"USPS"),"")DHLDHLDHL
7Item 6http://link.order.homedepot.com/Home Depot Express DeliveryApplication.WorksheetFunction.IFERROR(IF(FIND("usps",[@[Tracking Link]])<>0,"USPS"),"")Home Depot Express DeliveryHome Depot Express DeliveryHome Depot Express Delivery
8Item 7https://www.sf-international.com/SF-InternationalApplication.WorksheetFunction.IFERROR(IF(FIND("usps",[@[Tracking Link]])<>0,"USPS"),"")SF-InternationalSF-InternationalSF-International
9Item 8https://www.yuntrack.com/YunExpressApplication.WorksheetFunction.IFERROR(IF(FIND("usps",[@[Tracking Link]])<>0,"USPS"),"")YunExpressYunExpressYunExpress
10Item 9https://tools.usps.com/USPSApplication.WorksheetFunction.IFERROR(IF(FIND("usps",[@[Tracking Link]])<>0,"USPS"),"")USPSUSPSUSPS
11Item 10https://www.amazon.com/ef=ppx_yo_dt_b_track_package?_encoding=UTF8&packageIndex=0&shipmentId=DhL6CMTDLAmazonApplication.WorksheetFunction.IFERROR(IF(FIND("usps",[@[Tracking Link]])<>0,"USPS"),"")DHLDHLAmazon
Order Tracking
Cell Formulas
RangeFormula
C2:C11C2=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"),"" ))))))))))))
D2:D11D2=Carrier()
E2:E11E2=IFERROR(INDEX(Table1[Carrier],MAX(IF(ISNUMBER(SEARCH(Table1[Search],B2)),ROW(Table1[Carrier])-ROW(Table1[[#Headers],[Carrier]]),-1))),"-")
F2:F11F2=IFNA(LOOKUP(9^9,SEARCH(Table1[Search],[@[Tracking Link]]),Table1[Carrier]),"-")
G2:G11G2=IFNA(LOOKUP(9^9,SEARCH(Table2[Search],[@[Tracking Link]]),Table2[Carrier]),"-")


Lookup Table1:
Orders_test posted to Mr Excel Forum.xlsm
BC
13SearchCarrier
14homedepotHome Depot Express Delivery
15fedexFedex
16uspsUSPS
17amazonAmazon
18upsUPS
19canadapostCanada Post
20dhlDHL
21sf-internationalSF-International
22PickupPickup
23yuntrackYunExpress
24pochtaRussian Post
Order Tracking


Lookup Table2:
Orders_test posted to Mr Excel Forum.xlsm
BC
26SearchCarrier
27homedepot.comHome Depot Express Delivery
28fedex.comFedex
29usps.comUSPS
30amazon.comAmazon
31ups.comUPS
32canadapost.comCanada Post
33dhl-usa.comDHL
34sf-international.comSF-International
35PickupPickup
36yuntrack.comYunExpress
37pochta.ruRussian Post
Order Tracking
 
Upvote 0
Lucky_Irishman said:
Either idea will work if the lookup table uses the domain name ("amazon.com" instead of just "amazon"). That solved the problem.
Click to expand...
You cannot really be sure of that - see orange cells below.
Right hand table in this sample is Table6

Lucky_Irishman.xlsm
ABCDFGH
1ItemTracking LinkCarrier_BSALVCarrier_Peter_SSs_OldSearchCarrier
2Item 1https://www.fedex.com/apps/fedextrack/FedexFedexhomedepot.comHome Depot Express Delivery
3Item 2https://www.amazon.com/progress-tracker/AmazonAmazonfedex.comFedex
4Item 3https://wwwapps.endups.commercial.namazon.comback.fedex.com/etracking/UPSUPSusps.comUSPS
5Item 4https://www.pochta.ru/Russian PostRussian Postamazon.comAmazon
6ups.comUPS
7canadapost.comCanada Post
8dhl-usa.comDHL
9sf-international.comSF-International
10PickupPickup
11yuntrack.comYunExpress
12pochta.ruRussian Post
Sample
Cell Formulas
RangeFormula
C2:C5C2=IFERROR(INDEX(Table6[Carrier],MAX(IF(ISNUMBER(SEARCH(Table6[Search],B2)),ROW(Table6[Carrier])-ROW(Table6[[#Headers],[Carrier]]),-1))),"-")
D2:D5D2=IFNA(LOOKUP(9^9,SEARCH(Table6[Search],[@[Tracking Link]]),Table6[Carrier]),"-")


There may actually be other options, but if you would like those considered I would like to see some examples involving "Pickup" since you have not included anything extra with that in your Table2 above. Examples with any other variety would also help. For example, we had not seen anything like cell B11 in post #12 before. :)
 
Upvote 0
Peter_SSs said:
You cannot really be sure of that - see orange cells below.
Right hand table in this sample is Table6

Lucky_Irishman.xlsm
ABCDFGH
1ItemTracking LinkCarrier_BSALVCarrier_Peter_SSs_OldSearchCarrier
2Item 1https://www.fedex.com/apps/fedextrack/FedexFedexhomedepot.comHome Depot Express Delivery
3Item 2https://www.amazon.com/progress-tracker/AmazonAmazonfedex.comFedex
4Item 3https://wwwapps.endups.commercial.namazon.comback.fedex.com/etracking/UPSUPSusps.comUSPS
5Item 4https://www.pochta.ru/Russian PostRussian Postamazon.comAmazon
6ups.comUPS
7canadapost.comCanada Post
8dhl-usa.comDHL
9sf-international.comSF-International
10PickupPickup
11yuntrack.comYunExpress
12pochta.ruRussian Post
Sample
Cell Formulas
RangeFormula
C2:C5C2=IFERROR(INDEX(Table6[Carrier],MAX(IF(ISNUMBER(SEARCH(Table6[Search],B2)),ROW(Table6[Carrier])-ROW(Table6[[#Headers],[Carrier]]),-1))),"-")
D2:D5D2=IFNA(LOOKUP(9^9,SEARCH(Table6[Search],[@[Tracking Link]]),Table6[Carrier]),"-")


There may actually be other options, but if you would like those considered I would like to see some examples involving "Pickup" since you have not included anything extra with that in your Table2 above. Examples with any other variety would also help. For example, we had not seen anything like cell B11 in post #12 before. :)
Click to expand...


There are about 1500 entries in the actual table. All three formulas having matching results, exect for the example you provided. My original formula is the only one that works with that.
"Pick Up" is typed in instead of a tracking link. All three formulas work with that as well.

Orders_test posted to Mr Excel Forum.xlsm
ABCDE
1ItemTracking LinkCarrier _ OriginalCarrier_Peter_SSs Using Domain Name LookupCarrier _Peter_SSs Using Domain Name Lookup
2Item 1https://www.fedex.com/apps/fedextrack/FedexFedexFedex
3Item 2https://www.amazon.com/progress-tracker/AmazonAmazonAmazon
4Item 3https://wwwapps.ups.com/etracking/UPSUPSUPS
5Item 4https://www.pochta.ru/Russian PostRussian PostRussian Post
6Item 5http://www.dhl-usa.com/DHLDHLDHL
7Item 6http://link.order.homedepot.com/Home Depot Express DeliveryHome Depot Express DeliveryHome Depot Express Delivery
8Item 7https://www.sf-international.com/SF-InternationalSF-InternationalSF-International
9Item 8https://www.yuntrack.com/YunExpressYunExpressYunExpress
10Item 9https://tools.usps.com/USPSUSPSUSPS
11Item 10https://www.amazon.com/ef=ppx_yo_dt_b_track_package?_encoding=UTF8&packageIndex=0&shipmentId=DhL6CMTDLAmazonDHLAmazon
12Item 11https://wwwapps.endups.commercial.namazon.comback.fedex.com/etracking/FedexUPSUPS
13Item 12PickupPickupPickupPickup
Order Tracking
Cell Formulas
RangeFormula
C2:C13C2=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"),"" ))))))))))))
D2:D13D2=IFNA(LOOKUP(9^9,SEARCH(Table1[Search],[@[Tracking Link]]),Table1[Carrier]),"-")
E2:E13E2=IFNA(LOOKUP(9^9,SEARCH(Table2[Search],[@[Tracking Link]]),Table2[Carrier]),"-")
 
Upvote 0
From all your examples so far, it seems that the critical text is the last full 'couplet' before the first single "/" in the tracking link.

https://www.fedex.com/apps/fedextrack/
http://link.order.homedepot.com/
https://www.amazon.com/progress-tracker/
https://www.pochta.ru/
https://www.amazon.com/ef=ppx_yo_dt_b_track_package?_encoding=UTF8&packageIndex=0&shipmentId=DhL6CMTDL

If that is the case, then I think this would be more robust as it ensures that position and also ensures that that the text to be looked up is preceded by a "."

Note that Pickup is not required in the lookup table (Table8 for me this time)

Lucky_Irishman.xlsm
ABCDEF
1ItemTracking LinkCarrierSearchCarrier
2Item 1https://www.fedex.com/apps/fedextrack/Fedexhomedepot.comHome Depot Express Delivery
3Item 2https://www.amazon.com/progress-tracker/Amazonfedex.comFedex
4Item 3https://wwwapps.endups.commercial.namazon.comback.fedex.com/etracking/Fedexusps.comUSPS
5Item 4https://www.pochta.ru/Russian Postamazon.comAmazon
6Item 5 ups.comUPS
7Item 6PickupPickupcanadapost.comCanada Post
8Item 7https:wwwapps.ups.com/etracking/UPSdhl-usa.comDHL
9Item 8https://www.other.com/?sf-international.comSF-International
10Item 9http://link.order.homedepot.com/Home Depot Express Deliveryyuntrack.comYunExpress
11Item 10https://www.amazon.com/ef=ppx_yo_dt_b_track_package?_encoding=UTF8&packageIndex=0&shipmentId=DhL6CMTDLAmazonpochta.ruRussian Post
Sample (2)
Cell Formulas
RangeFormula
C2:C11C2=LET(TL,[@[Tracking Link]],s,TRIM(LEFT(TL,FIND("/",TL&REPT(" ",9)&"/",9)-1)),n,LEN(s)-LEN(SUBSTITUTE(s,".","")),IF(s="",s,IF(s="Pickup",s,IFNA(VLOOKUP(TRIM(RIGHT(SUBSTITUTE(s,".",REPT(" ",100),n-1),100)),Table8,2,0),"?"))))
 
Upvote 0
Solution
Peter_SSs said:
From all your examples so far, it seems that the critical text is the last full 'couplet' before the first single "/" in the tracking link.

https://www.fedex.com/apps/fedextrack/
http://link.order.homedepot.com/
https://www.amazon.com/progress-tracker/
https://www.pochta.ru/
https://www.amazon.com/ef=ppx_yo_dt_b_track_package?_encoding=UTF8&packageIndex=0&shipmentId=DhL6CMTDL

If that is the case, then I think this would be more robust as it ensures that position and also ensures that that the text to be looked up is preceded by a "."
Click to expand...
I think you have it. Thanks.
 
Last edited by a moderator:
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
XLOOKUP or VLOOKUP to return a hyperlink
Replies
2
Views
4K
thewiseguy
T

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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