HV_L
01-24-2012, 09:13 AM
Hi, I have in column A numbers from 1 -250
I add new rows by userform and then set several formula's and the form data in the new row.
I manually created links from this first row looking like this:
=HYPERLINK("D:\Documents\AV\154";"154")
How can I grab (with VBA) the used numbers in this link and create new link in the new row?
So, in this case the link created should look like
=HYPERLINK("D:\Documents\AV\155";"155")
Code used in the Form:
Private Sub OpslaanButton_Click() 'Make Sheet digid Active Sheets("digid").Activate Dim emptyRow As Long Dim ws As Worksheet Set ws = Worksheets("digid") 'find first empty row in database emptyRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1 With ws.[a1].CurrentRegion lRow = .Row + .Rows.Count .Cells(lRow, "C").Formula = "=IF(OFFSET(Klnr,ROW()-1,MATCH(""Opm"",Print_Titles,0)-1)<>"""",HYPERLINK(""#"" & CELL(""adres"",OFFSET(Klnr,ROW()-1,MATCH(""Opmerking"",Print_Titles,0)-1)),""bekijk""),"""")" .Cells(lRow, "M").Formula = "=IF(OFFSET(Klnr,ROW()-1,MATCH(""Opm"",Print_Titles,0)-1)<>"""",HYPERLINK(""#"" & CELL(""adres"",OFFSET(Klnr,ROW()-1,MATCH(""Klnr"",Print_Titles,0)-1)),""terug""),"""")" End With 'Export Forminput to worksheet Cells(emptyRow, 4).Value = Txtsofi.Value Cells(emptyRow, 5).Value = TxtVoorl.Value Cells(emptyRow, 6).Value = Txtnaam.Value Cells(emptyRow, 7).Value = Txtadres.Value Cells(emptyRow, 8).Value = Txtnr.Value Cells(emptyRow, 9).Value = TxtPostcode.Value Cells(emptyRow, 10).Value = Txtwoonpl.Value Cells(emptyRow, 11).Value = Txtdigid.Value Cells(emptyRow, 12).Value = Txtdigiww.Value Cells(emptyRow, 14).Value = TxtOpm.Value End Sub
I add new rows by userform and then set several formula's and the form data in the new row.
I manually created links from this first row looking like this:
=HYPERLINK("D:\Documents\AV\154";"154")
How can I grab (with VBA) the used numbers in this link and create new link in the new row?
So, in this case the link created should look like
=HYPERLINK("D:\Documents\AV\155";"155")
Code used in the Form:
Private Sub OpslaanButton_Click() 'Make Sheet digid Active Sheets("digid").Activate Dim emptyRow As Long Dim ws As Worksheet Set ws = Worksheets("digid") 'find first empty row in database emptyRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1 With ws.[a1].CurrentRegion lRow = .Row + .Rows.Count .Cells(lRow, "C").Formula = "=IF(OFFSET(Klnr,ROW()-1,MATCH(""Opm"",Print_Titles,0)-1)<>"""",HYPERLINK(""#"" & CELL(""adres"",OFFSET(Klnr,ROW()-1,MATCH(""Opmerking"",Print_Titles,0)-1)),""bekijk""),"""")" .Cells(lRow, "M").Formula = "=IF(OFFSET(Klnr,ROW()-1,MATCH(""Opm"",Print_Titles,0)-1)<>"""",HYPERLINK(""#"" & CELL(""adres"",OFFSET(Klnr,ROW()-1,MATCH(""Klnr"",Print_Titles,0)-1)),""terug""),"""")" End With 'Export Forminput to worksheet Cells(emptyRow, 4).Value = Txtsofi.Value Cells(emptyRow, 5).Value = TxtVoorl.Value Cells(emptyRow, 6).Value = Txtnaam.Value Cells(emptyRow, 7).Value = Txtadres.Value Cells(emptyRow, 8).Value = Txtnr.Value Cells(emptyRow, 9).Value = TxtPostcode.Value Cells(emptyRow, 10).Value = Txtwoonpl.Value Cells(emptyRow, 11).Value = Txtdigid.Value Cells(emptyRow, 12).Value = Txtdigiww.Value Cells(emptyRow, 14).Value = TxtOpm.Value End Sub