MobileSheets Forums

Full Version: Macro/code for offset/change pagenumbers in csv-files
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
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:
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):
        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.

Excellent idea, thanks for sharing.
Im sure I'll give it a try as soon as I need it (or find the time to just play around with it)