• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Macro/code for offset/change pagenumbers in csv-files
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)
first language: German
Acer A1-830, Android 4.4.2 - HP x2 210 G2 Detachable, Win 10 22H2 - Huawei Media Pad T5, Android 8.0 - Boox Tab Ultra C, Android 11
www.moonlightcrisis.de - www.basdjo.de - www.frankenbaend.de

A few notes:

To get the Developer Toolbar (on Excel for Mac):

Quote:Excel > Preferences > Ribbon & Toolbar > Customize Thie Ribbon > [check box for Developer]


‘Returned value is a string, not a number

This is a typo. This is a "smart quote". That will not compile. The leading character needs to be a single quote ('). Remember, "smart quotes are evil". 


This is another typo. That semi-colon needs to be a colon.

After that, I got this to run :-) 
By the way, I should have said THANK YOU for this script! It helped!

Users browsing this thread:
1 Guest(s)

  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2024 MyBB Group.