Looping Formula? VBA

[Zains]

Hey guys/girls,

My first post!

I was originally trying to run this formula, but found that I couldnt autofill a filtered selection. heres the code:

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

Maybe looping would be better?

I cant seem to autofill just the filtered choice.

I have about 20 values in Column G, and different data in Column H. Depending on what column G is, a different hyperlink URL has to be applied.

EG.

Cell G2: City Link
Cell H2: 12345

If Cell G2 = City Link, then Column I2 = HYPERLINK("http://www.packagetrackr.com/track/citylink/"&H2&")

Any suggestions would be fantastic!

Thanks guys/girls!

 

[ukmikeb]

Hi

Welcome to the forum.

Try this (untested) :-

Dim CourierRgIncHdgs As Range
Dim CourierRgExcHdgs As Range
Dim LR
LR = Range("A" & Rows.Count).End(xlUp).Row ' Last Row
Set CourierRgIncHdgs = Range("G1:I" & LR)
' Filter dpd
CourierRgIncHdgs.AutoFilter Field:=1, Criteria1:="dpd", VisibleDropDown:=False
Set CourierRgExcHdgs = ActiveSheet.AutoFilter.Range.Offset(1, 2).Resize(ActiveSheet.AutoFilter.Range.Rows.Count - 1, 1)
With CourierRgExcHdgs.SpecialCells(xlCellTypeVisible)
 .FormulaR1C1 = _
        "=HYPERLINK(""&""&search.searchType=16&search.javascriptValidated=0&appmode=guest]DPD (UK) - Tracking - Track It""))"
End With
' Reset Autofilter
CourierRgIncHdgs.AutoFilter
' Filter City Link
CourierRgIncHdgs.AutoFilter Field:=1, Criteria1:="City Link", VisibleDropDown:=False
    Set CourierRgExcHdgs = ActiveSheet.AutoFilter.Range.Offset(1, 2).Resize(ActiveSheet.AutoFilter.Range.Rows.Count - 1, 1)
With CourierRgExcHdgs.SpecialCells(xlCellTypeVisible)
    .FormulaR1C1 = _
        "=HYPERLINK(""http://www.packagetrackr.com/track/citylink/""&R[-1]C[-1]&""""))"
End With
' Reset Autofilter
CourierRgIncHdgs.AutoFilter
' Filter Hermes
CourierRgIncHdgs.AutoFilter Field:=1, Criteria1:="Hermes", VisibleDropDown:=False
Set CourierRgExcHdgs = ActiveSheet.AutoFilter.Range.Offset(1, 2).Resize(ActiveSheet.AutoFilter.Range.Rows.Count - 1, 1)
With CourierRgExcHdgs.SpecialCells(xlCellTypeVisible)
    .FormulaR1C1 = _
        "=HYPERLINK(""C[-1]&""&Postcode=""&R[-1]C[-4]&R[-1]C[-3]Hermes : Parcel Tracking]&""""))"
End With
' Reset Autofilter
CourierRgIncHdgs.AutoFilter

Basically I've concentrated only on the cells you're interested in ie columns G to I.
I've removed the test for the Courier as that is redundant and left you with the HYPERLINK.

hth

 

[Zains]

Hi Mike, thank you for your response.

I haven't tested it yet, the only possible limitation I can see is that the values in column G will not always be the same..for example the courier DPD may not be used all the time..will this cause a run time error if the macro attempts to select DPD with a filter and it can't find it?

Because this data will keep changing daily, thats why I attempted to use the IF formula for the couriers.

Thank you

 

[ukmikeb]

I haven't tested it yet, the only possible limitation I can see is that the values in column G will not always be the same..for example the courier DPD may not be used all the time..will this cause a run time error if the macro attempts to select DPD with a filter and it can't find it?

Because this data will keep changing daily, thats why I attempted to use the IF formula for the couriers.

It shouldn't be a problem as it will return zero records. If that causes the "Set Range" to error then we could easily cater for it.

Btw, did you set column I to "FALSE" before you Autofiltered?

 

[ukmikeb]

Hi

I did some further checking for zero rows and found that it would have resulted in an error. Accordingly, I have adjusted the code to cater for that situation.

Dim CourierRgIncHdgs As Range
Dim CourierRgExcHdgs As Range
Dim Fr, LR
LR = Range("A" & Rows.Count).End(xlUp).Row ' Last Row
Set CourierRgIncHdgs = Range("G1:I" & LR)
' Filter dpd
CourierRgIncHdgs.AutoFilter Field:=1, Criteria1:="dpd", VisibleDropDown:=False
 Fr = Range("G" & Rows.Count).End(xlUp).Row - 1
 '        No rows filtered then Fr = 0 
If Fr > 0 Then
    Set CourierRgExcHdgs = ActiveSheet.AutoFilter.Range.Offset(1, 2).Resize(ActiveSheet.AutoFilter.Range.Rows.Count - 1, 1)
    With CourierRgExcHdgs.SpecialCells(xlCellTypeVisible)
         .FormulaR1C1 = _
        "=HYPERLINK(""&""&search.searchType=16&search.javascriptValidated=0&appmode=guest]DPD (UK) - Tracking - Track It""))"
    End With
End If
' Reset Autofilter
CourierRgIncHdgs.AutoFilter
' Filter City Link
CourierRgIncHdgs.AutoFilter Field:=1, Criteria1:="City Link", VisibleDropDown:=False
 Fr = Range("G" & Rows.Count).End(xlUp).Row - 1
 '        No rows filtered then Fr = 0 
 If Fr > 0 Then
    Set CourierRgExcHdgs = ActiveSheet.AutoFilter.Range.Offset(1, 2).Resize(ActiveSheet.AutoFilter.Range.Rows.Count - 1, 1)
    With CourierRgExcHdgs.SpecialCells(xlCellTypeVisible)
        .FormulaR1C1 = _
        "=HYPERLINK(""http://www.packagetrackr.com/track/c...91;-1]C[-1]&""""))"
    End With
End If
' Reset Autofilter
CourierRgIncHdgs.AutoFilter
' Filter Hermes
CourierRgIncHdgs.AutoFilter Field:=1, Criteria1:="Hermes", VisibleDropDown:=False
 Fr = Range("G" & Rows.Count).End(xlUp).Row - 1
 '        No rows filtered then Fr = 0 
 If Fr > 0 Then
    Set CourierRgExcHdgs = ActiveSheet.AutoFilter.Range.Offset(1, 2).Resize(ActiveSheet.AutoFilter.Range.Rows.Count - 1, 1)
    With CourierRgExcHdgs.SpecialCells(xlCellTypeVisible)
        .FormulaR1C1 = _
        "=HYPERLINK(""C[-1]&""&Postcode=""&R[-1]C[-4]&R[-1]C[-3]Hermes : Parcel Tracking]&""""))"
    End With
End If
' Reset Autofilter
CourierRgIncHdgs.AutoFilter

hth

 

[Zains]

Thanks Mike..will test this now! thanks again!

 

[Zains]

Hi Mike..this works perfectly! Just one slight hiccup, it doesnt hyperlink the formula, and sometimes gives me an error code regarding certain formulas. eg:

.FormulaR1C1 = _
"=HYPERLINK(""http://www.hermes-europe.co.uk/tracker.html?trackingNumber=""&RC[-1]&""&Postcode=""&RC[-4]&RC[-3]&""""))"
End With

It doesnt like that formula for some reason. The DPD one works perfectly, however no hyperlink.
What do you think the solution could be?
The rest of it works fine as I've added more filters (more couriers) and it locates them fine and enters a value into column I accordingly. All i need to do now is insert the formulas, but it doesnt seem to take them.

Any help would be appreciated.

Thanks again!

 

[Zains]

Hi Mike, No worries about this. I found another possible way of applying this formula.

Thanks for all your help, much appreciated!

 

[Zains]

Hey Mike, back here again haha.
Seems like my other formula didnt work out as smoothly as planned. Any thoughts on the above?

Thanks

 

[ukmikeb]

Hi

Perhaps go back to specifying the formula with the "IF".

I notice that there are unwanted values in all the formulae, probably due to a Java installation which failed to install, now corrected.

It's also probably best checking that the fields for the Hyperlink are completing correctly.

hth