convert numbers in a table to hyperlinks to other workbooks

[turkishjim]

I'm a total beginner to VBA but fairly comfortable with Excel. I have a worksheet listing my customers' details in columns A to D (one customer per row, sorted alphabetically), then in column E onwards I have the invoice numbers of their previous orders: some only 1 column but a few with more than 10 orders. The invoices are Excel workbooks all stored in the same folder on my PC.
What I would like to do is simplify the task of referring to previous orders by making each cell in columns E onwards into a hyperlink to the actual invoice. The path would be "file:///D:\Business\Accounts\.....\InvoiceXXXX.xlsx" where XXXX is the invoice number which currently populates the cell. I am happy to create the hyperlinks as I add new data to the worksheet but am looking for an easy (lazy) way to convert my existing data to hyperlinks as a one-off exercise, leaving blank cells blank.
I don't think this is possible using formulas (but I could be wrong) but wondered if it is possible using VBA.
Any assistance would be welcome.

 

[offthelip]

try this code (untested)

Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
  For i = 2 To lastrow ' I have assumed a row for headers
  lastcol = lastcol = Cells(i, Columns.Count).End(xlToLeft).Column
            
             For j = 5 To lastcol
               XXXX = Cells(i, j)
              if XXXX <> "" then              
                        '   now add links
              With ActiveSheet
              .Hyperlinks.Add Anchor:=.Range(Cells(i, j), Cells(i, j)), _
                                Address:="file:///D:\Business\Accounts\.....\Invoice" & XXXX & ".XLSX", _
                                TextToDisplay:=XXXX
              End With
              end if
              Next j
   Next i

End Sub

 

[turkishjim]

Thank you, offthelip, for your suggestion and apologies for not getting back to you sooner: managed to break my workbook and had to spend some time getting it back together.
I have run the macro in a copy of my worksheet, but nothing appears to happen. So you know I have inserted the full hyperlink address, and my range runs from cell E2 (which I assume relates to your i=2 and j=5) to cell BB1932. I would be grateful if you could suggest any tweaks to make this work. My other option is to insert helper columns between each pair of columns and use a formula like =HYPERLINK("file:///D:\Business\Accounts\Invoices\composite\invoice"&E2&".xlsx",E2), but it would be much neater to simply change the existing cells.

Thank you in advance if you are able to help further.

 

[offthelip]

I am assuming that you don't have an "On error" statement in your code, if you do delete or comment it out.
There are only three possible things that could cause this macro to "do nothing"
Firstly lastrow is not set to the correct value. This check for data in column A, if there is nothing in column A then this macro does nothing
Secondly if the lastcol check comes back with a number less than 5, check that there is data in column E and beyond in row 2 onwards
Thirdly the check:

if XXXX <> "" then

never becomes true, this relies on data being in E2, F2, G2 etc and then E3,F3, ETC
If you try to create a hyperlink and something is not correct with the variables then an error message will appear, so I very much doubt you have got to that statement.
The easiest way to find out what is happening is to set a breakpoint on the first line, then step through the macro one line at a time by typing the F8 key. If you hover over the variables you can see what they are . So check lastrow and lastcol are correct, check that XXXX is correct

 

[turkishjim]

Many thanks for your help again. As I said initially I really am a newbie to VBA and only have a very limited understanding of the syntax in your code.
I have stepped through the macro as you suggest and found that the variables come back as lastrow=1932 (which is correct) but lastcol=false is clearly the problem or part of it. XXXX=Empty, presumably as a result of the lastcol issue. As I mentioned, not every row has data as far as column BB, and in fact column F is first populated on row 6, column G n row 7 and so on. Could this be the issue or a contributing factor?

 

[offthelip]

I should have spotted that, it was a cut and paste error:
this statement:

lastcol = lastcol = Cells(i, Columns.Count).End(xlToLeft).Column

should be:

lastcol = Cells(i, Columns.Count).End(xlToLeft).Column

 

[turkishjim]

Thanks, offthelip. That has made things better, but I am now getting a "runtime error (5): Invalid procedure call or argument" at the line beginning ".Hyperlinks". lastcol is returning a value of 5, which may well be correct but looks to me to be the first column.

 

[offthelip]

I think that error is because the path you are giving is not a valid path: You posted the path as being:
"file:///D:\Business\Accounts\.....\Invoice"
However this is not a valid path, try getting rid of the file:/// at the front of it and starting:
"D:\Business\Accounts\.....\Invoice"
obviously you need to put something instead of the dots as well