expertatexcel.com

AddLinks - convert Jira into links

This routine converts data into Hyperlinks to the system to retrieve the data

https://cherper.com/program//overview
https://cherper.com/project//overview
https://cherper.com/project//de/edit/
https://cherper.com/project//de//deploy/

Set start_cell = Range("hyperlink_start")
link_col = start_cell.column

Set end_cell = Range("hyperlink_end")

sRow = start_cell.Row
eRow = end_cell.Row

for i = sRow to eRow - 1
    ThisWorkbook.Activate
    sField = Cells(i, link_col).value
    sLink = Cells(i, link_col + 1).value
    Workboos(sWB).Activate
    lCol = FindDataCol(sField, 1)
    If lcol = 0 then stop
    r = FindLastRow(1)
    Range("A1").select
    lStart = GetNextRow() ' positions to the next unhidden row to deal with filtered data
    Do While ActiveCell.Row <= r
       j = ActiveCell.row
       Cells(j, lCol).select
        If ActiveSheet.Rows(j).Hidden then
        Else
            progId = Cells(j, prog_col)
            projId = Cells(j, proj_col)
            evid   = Cells(j, event_col)
            died   = Cells(j, de_col) & ""
            sAddress = Replace(Replace(sLink, "", progId), "", projid)
            sAddress = Replace(sAddress, "", evid)
            sAddress = Replace(sAddress, "", deid)
            if ActiveCell = "" Then
            Else
                ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=sAddress, TextToDisplay:=CStr(ActiveCell.Value)
            End If
            x = GetNextRow()
    Loop
Next
End Sub