VBA代码根据单元格值创建公式 [英] VBA code to create formula depending on cell value
问题描述
我有一个电子表格，其中包含单元格A到G中的值。
I have a spreadsheet with values in cells A to G.
我需要读取单元格F中的数据，并根据数据制定一个超链接到H列中的相应单元格，单元格G中的数据（F旁边）插入超链接。
I need to read the data in Cell F, and depending on the data, formulate a hyperlink into the corresponding cell in column H, with the data from cell G (next to F) inserted within the hyperlink.
EG 1
Cell F包含：DPD
Cell F contains : DPD
Cell g包含：123456
Cell g contains : 123456
希望单元格H创建超链接：
Desire cell H to create hyperlink :
= HYPERLINK（ http://www.dpd.co.uk/tracking/quicktrack.do?search.consignmentNumber= &安培; CELLG *安培; &安培; search.searchType = 16&安培; search.javascriptValidated = 0& appmode = guest）
=HYPERLINK("http://www.dpd.co.uk/tracking/quicktrack.do?search.consignmentNumber="&CELLG*&"&search.searchType=16&search.javascriptValidated=0&appmode=guest")
EG 2
Cell F包含：TNT
Cell F contains : TNT
Cell g包含：abcde
Cell g contains : abcde
希望单元格H创建超链接：
Desire cell H to create hyperlink :
= HYPERLINK（ http：/ /www.tnt.com/express/en_gb/site/searchresults.html?q= CELLG ）
=HYPERLINK("http://www.tnt.com/express/en_gb/site/searchresults.html?q=CELLG")
我有单元格F中的更多其他值依赖于不同的超链接公式，但我应该能够重复和更改代码以满足这些值。
I have many more other values in cell F which rely on a different hyperlink formula, but I should be able to repeat and alter the code to cater for those values.
更新：
这是我目前尝试应用公式。
This is my current attempt at applying the formula.
Range("I1").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]=""dpd"",HYPERLINK(""http://www.dpd.co.uk/tracking/quicktrack.do?search.consignmentNumber=""&RC[-1]&""&search.searchType=16&search.javascriptValidated=0&appmode=guest""))"
Range("I1").Select
Selection.AutoFill Destination:=Range("i1:i" & Range("A" & Rows.Count).End(xlUp).Row), Type:=xlFillCopy
Range("I2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1:I1").Select
ActiveSheet.Range("A1:i1").AutoFilter Field:=9, Criteria1:="FALSE"
Range("I2").Select
ActiveCell.FormulaR1C1 = _
"=IF(R[-1]C[-2]=""City Link"",HYPERLINK(""http://www.packagetrackr.com/track/citylink/""&R[-1]C[-1]&""""))"
Range("I2").Select
Selection.AutoFill Destination:=Range("i2:i" & Range("A" & Rows.Count).End(xlUp).Row), Type:=xlFillCopy
Range("A1:I1").Select
ActiveSheet.Range("A1:i1").AutoFilter Field:=9, Criteria1:="FALSE"
Range("i2").Select
ActiveCell.FormulaR1C1 = _
"=IF(R[-1]C[-2]=""hermes"",HYPERLINK(""http://www.hermes-europe.co.uk/tracker.html?trackingNumber=""&R[-1]C[-1]&""&Postcode=""&R[-1]C[-4]&R[-1]C[-3]&""""))"
Range("I2").Select
Selection.AutoFill Destination:=Range("i2:i" & Range("A" & Rows.Count).End(xlUp).Row), Type:=xlFillCopy
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
Range("I2").Select
Selection.Delete Shift:=xlUp
Range("I1").Select
我无法弄清楚如何在过滤列表上自动填充。
I cannot figure out how to autofill on a filtered list.
也许我需要尝试循环方法？
Maybe I need to try the loop through method?
失败的公式：
= IF（G2 =hermes，HYPERLINK（www.hermes-europe.co.uk/ tracker.html trackingNumber =？ &安培; H2&安培; &安培;邮编= &安培; E2&安培; F2&安培;），IF（G2 = 英国邮政包裹公司，HYPERLINK（ www.parcelforce.com/track-trace?trackNumber= & h2&），IF（G2 =皇家邮政，HYPERLINK（www.royalmail.com/track-trace?track=track&trackNumber =& h2&& gear = track& imageRootPath =& ; LOC = EN_GB&安培;默认=默认&安培; EMT = EMT ），IF（G2 = 约德尔，HYPERLINK（ www.myyodel.co.uk/tracking?parcel_id = &安培; H2&安培; &安培;邮政编码= &安培; e2&％23& f2&），IF（G2 =4 Square，HYPERLINK（login.s） martconsign.co.uk/trackingcust.aspx?partnerid=EAFSQ001&jobno=\"&h2&\"),IF(G2=\"DeutschePost，HYPERLINK（nolp.dhl.de/nextt-online-public/set_identcodes。 do？lang = en& idc =& h2&& rfn =& extendedSearch = true），IF（g2 =No Tracking，& h2&）））））
=IF(G2="hermes",HYPERLINK("www.hermes-europe.co.uk/tracker.html?trackingNumber="&H2&"&Postcode="&e2&f2&"),IF(G2="Parcelforce",HYPERLINK("www.parcelforce.com/track-trace?trackNumber="&h2&"),IF(G2="Royal Mail",HYPERLINK("www.royalmail.com/track-trace?track=track&trackNumber="&h2&"&gear=track&imageRootPath=&loc=en_GB&default=default&emt=emt"),IF(G2="Yodel",HYPERLINK("www.myyodel.co.uk/tracking?parcel_id="&h2&"&postcode="&e2&"%23"&f2&"),IF(G2="4 Square",HYPERLINK("login.smartconsign.co.uk/trackingcust.aspx?partnerid=EAFSQ001&jobno="&h2&"),IF(G2="Deutsche Post",HYPERLINK("nolp.dhl.de/nextt-online-public/set_identcodes.do?lang=en&idc="&h2&"&rfn=&extendedSearch=true"),IF(g2="No Tracking","&h2&")"""))))
推荐答案
您必须使用相关单元格的地址而不是 CELLG 。请参阅此示例。
You have to use the relevant cell's address rather than CELLG. See this example.
这将进入单元格 H1
要在Excel公式中进行比较，您可以使用 IF 公式
To do a comparision in an Excel formula you can use the IF formula
示例
=如果（条件，如果Cond为真，如果Cond为假，则执行）
所以
= IF（F2 =TNT，如果等于TNT则做某事，如果不等于TNT则做某事））
或
= IF（F2 =DPD，Do Something如果等于DPD，Do Something如果不等于DPD））
结合以上两个将给出
= IF（F2 =TNT，如果等于TNT，则执行某些操作，IF（F2 =DPD，如果等于DPD则执行某些操作，做某事，如果不等于DPD）））
如果TNT然后使用这个网站
If TNT then use this website
http://www.tnt.com/express/en_gb/site/searchresults.html?q=
如果DPD然后使用这个网站
If DPD then use this website
http://www.dpd.co.uk/tracking/quicktrack.do?search 。 consignmentNumber =
我们现在所要做的就是将它附加到上面的公式
All we have to do now is append it to the above formula
= IF（F1 = TNT，HYPERLINK（ HTTP：// www 的&安培; F1&安培; ？的.com /表达/ EN_GB /站点/ searchresults.html q = &安培;
G1&
& search.searchType = 16& search.javascriptValidated = 0& appmode = guest），IF（F1 =DPD，HYPERLINK（http：// www。& F1&.co .uk / tracking / quicktrack.do？search.consignmentNumber =&
G1&
& search.searchType = 16& search.javascriptValidated = 0& appmode = guest），） ）
屏幕截图
这篇关于VBA代码根据单元格值创建公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
