Hyperlink & Vlookup help

[Elmer]

OK I have a big problem and nobody has been able to solve it yet.

I have my archive music charts in word format and I want to use excel. Simple enough I just copy and paste the chart onto excel. Thats easy. I also have songs into mp3 format hyperlinked from the name on the word document to play my mp3 when I click on it.

My problem starts when I use the Vlookup function so I only have to type in last weeks chart positions it copies the names OK but it doesn't trannpose the hyperlink. Can anyone help

e.g top 5 3-1-63

1 2 THE NEXT TIME / BACHELOR BOY Cliff Richard
2 1 RETURN TO SENDER Elvis Presley
3 5 LOVESICK BLUES Frank Ifield
4 7 SUN ARISE Rolf Harris
5 4 DANCE WITH THE GUITAR MAN Duane Eddy

then I use a Vlookup command and the top 5 the following week looks like this


1 1 THE NEXT TIME / BACHELOR BOY Cliff Richard
2 2 RETURN TO SENDER Elvis Presley
3 7 DANCE ON Shadows
4 5 DANCE WITH THE GUITAR MAN Duane Eddy
5 3 LOVESICK BLUES Frank Ifield

The vlookup has worked but the hyperlinks havent!!

Any experts out there ?

 

[Elmer]

It seems to work up until there isn't a previous entry i.e upto 22 then it says

Run Time Error 1004

Unable to get the match property of the Worksheet Function class

when I press debug it doesn't like this particular command


r = WF.Match(c.Value, Range("A4:A43"), 0)

 

[Elmer]

I seem to have got round it by giving new entries false numbers then subbing new in afterwards

 

[Andrew Poulsom]

You could also put:

On Error Resume Next

at the beginning of the code.

 

[Elmer]

On Error Resume next has the effect of copying the exact entry from the last position into the next and overriding what was written before!!

 

[Elmer]

I think I have sussed it now. Thanks for all your help mate!!