title: VBA code to create formula depending on cell value
body: <p>I have a spreadsheet with values in cells A to G.</p>
  
  <p>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.</p>
  
  <p>EG 1</p>
  
  <p>Cell F contains : DPD</p>
  
  <p>Cell g contains : 123456</p>
  
  <p>Desire cell H to create hyperlink :</p>
  
  <p>=HYPERLINK("<a href="http://www.dpd.co.uk/tracking/quicktrack.do?search.consignmentNumber=" rel="nofollow">http://www.dpd.co.uk/tracking/quicktrack.do?search.consignmentNumber=</a>"&amp;CELLG*&amp;"&amp;search.searchType=16&amp;search.javascriptValidated=0&amp;appmode=guest")</p>
  
  <p>EG 2</p>
  
  <p>Cell F contains : TNT</p>
  
  <p>Cell g contains : abcde</p>
  
  <p>Desire cell H to create hyperlink :</p>
  
  <p>=HYPERLINK("<a href="http://www.tnt.com/express/en_gb/site/searchresults.html?q=" rel="nofollow">http://www.tnt.com/express/en_gb/site/searchresults.html?q=</a><strong>CELLG</strong>")</p>
  
  <p>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.</p>
  
  <p>UPDATED:</p>
  
  <p>This is my current attempt at applying the formula.</p>
  
  <pre><code>  Range("I1").Select
  ActiveCell.FormulaR1C1 = _
      "=IF(RC[-2]=""dpd"",HYPERLINK(""http://www.dpd.co.uk/tracking/quicktrack.do?search.consignmentNumber=""&amp;RC[-1]&amp;""&amp;search.searchType=16&amp;search.javascriptValidated=0&amp;appmode=guest""))"
  Range("I1").Select
  Selection.AutoFill Destination:=Range("i1:i" &amp; Range("A" &amp; 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/""&amp;R[-1]C[-1]&amp;""""))"
    Range("I2").Select
  Selection.AutoFill Destination:=Range("i2:i" &amp; Range("A" &amp; 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=""&amp;R[-1]C[-1]&amp;""&amp;Postcode=""&amp;R[-1]C[-4]&amp;R[-1]C[-3]&amp;""""))"
    Range("I2").Select
  Selection.AutoFill Destination:=Range("i2:i" &amp; Range("A" &amp; 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
  </code></pre>
  
  <p>I cannot figure out how to autofill on a filtered list.</p>
  
  <p>Maybe I need to try the loop through method?</p>
  
  <p>FAILED FORMULA:</p>
  
  <p>=IF(G2="hermes",HYPERLINK("www.hermes-europe.co.uk/tracker.html?trackingNumber="&amp;H2&amp;"&amp;Postcode="&amp;e2&amp;f2&amp;"),IF(G2="Parcelforce",HYPERLINK("www.parcelforce.com/track-trace?trackNumber="&amp;h2&amp;"),IF(G2="Royal Mail",HYPERLINK("www.royalmail.com/track-trace?track=track&amp;trackNumber="&amp;h2&amp;"&amp;gear=track&amp;imageRootPath=&amp;loc=en_GB&amp;default=default&amp;emt=emt"),IF(G2="Yodel",HYPERLINK("www.myyodel.co.uk/tracking?parcel_id="&amp;h2&amp;"&amp;postcode="&amp;e2&amp;"%23"&amp;f2&amp;"),IF(G2="4 Square",HYPERLINK("login.smartconsign.co.uk/trackingcust.aspx?partnerid=EAFSQ001&amp;jobno="&amp;h2&amp;"),IF(G2="Deutsche Post",HYPERLINK("nolp.dhl.de/nextt-online-public/set_identcodes.do?lang=en&amp;idc="&amp;h2&amp;"&amp;rfn=&amp;extendedSearch=true"),IF(g2="No Tracking","&amp;h2&amp;")"""))))</p>