How would I enter this worksheet formula in VBA? Hyperlink within workbook

[robertgama]

After I process data, I'm putting links back to the original data so the user can fix the values if there were errors.


Here is the worksheet formula in column F and in this case row 6:

=IF(E6="Requires Fix",HYPERLINK(aaGetHyperlinkLocation(Clients!H2),Clients!H2),"")

aaGetHyperlinkLocation is just a UDF to return a properly formatted location for the hyperlink function from a range.

I would like to be able to do the same with VBA, resulting in a hyperlink the the cells back to 'Clients!H2'. How would that be accomplished with VBA?

Thanks,

Rob.

 

[Norie]

Rob

To enter that formula in a cell.

Range("A2").Formula ="=IF(E6=""Requires Fix"",HYPERLINK(aaGetHyperlinkLocation(Clients!H2),Clients!H2),"""")"

If that's not what you want you'll need to explain a bit more, for example what exactly does aaGetHyperLinkLocation return?

 

[robertgama]

Thanks Norie, I'll test that out.

I'll post what the helper function returns (just the format that the function is expecting):

Public Function aaGetHyperlinkLocation(myRange As Range) As String

        If aaGetSheetName(myRange) = Application.ActiveSheet.Name Then
            aaGetHyperlinkLocation = "[" + aaGetFileName() + "]" + myRange.Address
        Else
            aaGetHyperlinkLocation = "[" + aaGetFileName() + "]'" + aaGetSheetName(myRange) + "'!" + myRange.Address
        End If
    
End Function

Thanks again,

Rob.

 

[robertgama]

Thanks again. It worked out nicely.

Since I already had the previous columns' value in an array, I did the following:

For j = 1 To numRecords
        'many more lines of code...

        If fixedProvinces(j, 1) = "Requires Fix" Then
            hyperlinkFixProvinces(j, 1) = "=HYPERLINK(aaGetHyperlinkLocation(Clients!H" & CStr(j + 1) & "),Clients!H" & CStr(j + 1) & ")"
        End If

        'many more lines of code...
       
Next j
'...
Range("F" + CStr(startRow) + ":F" + CStr(lastRow)).Formula = hyperlinkFixProvinces