[Pflichtfeld]

Hi out there,

I found out, that there are many csv-files for many books. But I recognized, that people have different pdf-files of these books with the songs in correct sequence, but with another page-offset, that means, the songX starts in one pdf on page 1, in another on page 4 and in a third on page 11.

To help me in this situation, i coded a little macro, which does this offset stuff, not only for songs on a single page, but also for a song that nees two or more pages, f.i. songX 1-3 is set to 9-11.

If anybody likes, he may use this code. Please do not ask me for support. The code works fine but you should be experienced with the development environment.

That is a brief howto:

• Open the csv-file in excel
  

• Open developer-tools, open window "visual basic for applications"
  

• Add a module and put this code into the module:
  

Code:

Function AddMult(wert As String, diff As Integer)
   Dim ff() As String, ii As Integer, tx As String
   ff = Split(wert, "-")
   For ii = 0 To UBound(ff)
      If tx <> "" Then tx = tx & "-"
      tx = tx & (Val(ff(ii)) + diff)
   Next ii
   AddMult = tx
   ‘Returned value is a string, not a number
End Function

• Go back to your sheet
  

• Put this in an empty cell in the row with the first pagenumber, (easiest would be next column to the number of the page):
  

         “=AddMult(ZZ;XX)”
        where ZZ is the adress of the cell with the first pagenumber, XX is the cipher you want to add (the offset) (negativ for subtraction).

• Fill out (with this formula) all the cells downwards to the row with the last pagenumber (STRG + U).
  

• Now (this step is important, because you later delete the cells with the original pagenumbers and also the cells with the formula): copy the entire cells with the new pagenumbers, and INSERT THEM  right next to the cells with the formulas AS VALUES:
  

• Delete the rows with the original pagenumbers and the rows with the formulas.
  

• Bring the sheet in a correct form, finish and enjoy.
  

TH