03-04-2022, 07:48 AM
(This post was last modified: 03-04-2022, 08:09 PM by 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:
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).
TH
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):
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
