Extracting Numbers
 

Hello, I've run into some trouble again. I have hyperlinks that point to
data from the same folders in different spreadsheets. The row order of the
spreadsheets will be rearranged by other users which will make the hyperlinks
redirrect the user to the wrong entry. I am trying to create a sub that will
repair the links and although my first idea did work, it took nearly a minute
per spreadsheet. I beleive I've found a way to write a sub that is much
faster but the big hurdle I face is extracting the row number from a
hyperlink on another spreadsheet. My Hyperlink formual looks like this (
=HYPERLINK("#'Check Sheet'!A60:R60", "Name Insured") ). With my spreadsheets
nearing a thousand rows I was wondering if there is a simple way I can just
have non-numeric characters dropped. Does anyone have any ideas. Anything
would be greatly appreciated.

Thanks,
Abode

Extracting Numbers
 

Ah Crap. Correction. Is there an easier way than the code shown below.
Everytime I seem to post for help here I figure out a decent answer right
afterwords. This seems rather a round-about way of doing it but as far as I
can see its rather resource efficient and ~shouldn't~ cause any errors. If
there is a better way, please let me know. Or if you can see how this way
will cause me any problems please let me know. So here is the little test
sub I just ran.


Public Sub HyperlinkEdit()
Dim i As Integer
Dim LeftHLArr As Variant, RowArr As Variant


''' =HYPERLINK("#'Check Sheet'!A60:R60", "Name Insured")
LeftHLArr = Split(Range("B2").Formula, ":R")

''' LeftHLArr(0) is =HYPERLINK("#'Check Sheet'!A60
RowArr = Split(LeftHLArr(0), "!A")

''' RowArr(1) is 60
i = RowArr(1)

MsgBox i

End Sub






"Abode" wrote:

Hello, I've run into some trouble again. I have hyperlinks that point to
data from the same folders in different spreadsheets. The row order of the
spreadsheets will be rearranged by other users which will make the hyperlinks
redirrect the user to the wrong entry. I am trying to create a sub that will
repair the links and although my first idea did work, it took nearly a minute
per spreadsheet. I beleive I've found a way to write a sub that is much
faster but the big hurdle I face is extracting the row number from a
hyperlink on another spreadsheet. My Hyperlink formual looks like this (
=HYPERLINK("#'Check Sheet'!A60:R60", "Name Insured") ). With my spreadsheets
nearing a thousand rows I was wondering if there is a simple way I can just
have non-numeric characters dropped. Does anyone have any ideas. Anything
would be greatly appreciated.

Thanks,
Abode