tjdickinson
Board Regular
- Joined
- Jun 26, 2021
- Messages
- 61
- Office Version
- 365
- Platform
- Windows
I'm trying to create a macro for generating call numbers for books in our school library based on data entered through a userform. Call numbers need to be unique, and there are certain standards for how to modify the parts to avoid duplication (so I can't just add an index value). I'm looking for code which I can add to two places in an existing function to do the following (both cases have similar things, which is why I include them in one post):
Case 1:
The user has input a value stored in variable cutter. (The cutter begins with the first letter of the author's surname followed by three numbers.) This needs to be compared to the other extant cutters in column C. If there are no duplicates, move on. If a duplicate exists, then the input variable author needs to be compared to the existing author in column G. (If there is no author, then the variable title is compared to column F. I'll refer only to author, but in each case, 'title' should be understood as well.) If the authors are the same, then move on. (NB: the author is input in the format "Surname, Firstname".)
Here's where it gets complicated: if the authors are not the same, then a digit needs to be added to the end of the cutter (variable). The digit cannot be 1 or 0 (as they can be confused with lower case L or capital O); usually it starts at 5, because any further duplicates result in changing that 5 to a different number such that the authors are sorted alphabetically. So if the next pass of checking results in a duplicate, then the authors need to be compared, and the 5 changed to a 3 if the author comes first alphabetically (compared to the value in column G) or to an 8 if it comes second alphabetically. After another round of checking, the number is changed again if duplicated, and again following alphabetical sequence. After this, if there's still a duplicate, then the macro should continue to run. At the end of the code, when all the variables are saved into the last row, a note should be added in column O stating that the cutter duplicates row x (wherever the last duplicate occurred).
Case 2:
The variable wkMark (work mark) is created in the code and it initially has the value of the first letter of the title of the book; it eventually gets stored in column D. The wkMark and cutter are merged in the variable cutWrk (i.e.
Now the cutWrk needs to be compared to the mergers of columns C & D of the extant records. If there's a duplicate, then the wkMark takes the first two letters of the title (and cutWrk is redefined), and it checks again. If there's another duplicate, then it compares the input variable year to the value in column I. If the years are different, then
If they are the same, then we're back to the added digit just like in case 1, starting with a 5, and then adjusting the digit up or down to sort alphabetically in case of additional duplicates. This digit gets stored in the variable cutApp and results in:
The main thing I can't figure out is how to modify a variable in different ways after each pass of checking for duplicates, since it's always conditional on the number of passes and in what ways the duplicates occur. I'm still rather novice at VBA coding, so I'm grateful for any help you can provide. Thanks in advance. Cheers.
Case 1:
The user has input a value stored in variable cutter. (The cutter begins with the first letter of the author's surname followed by three numbers.) This needs to be compared to the other extant cutters in column C. If there are no duplicates, move on. If a duplicate exists, then the input variable author needs to be compared to the existing author in column G. (If there is no author, then the variable title is compared to column F. I'll refer only to author, but in each case, 'title' should be understood as well.) If the authors are the same, then move on. (NB: the author is input in the format "Surname, Firstname".)
Here's where it gets complicated: if the authors are not the same, then a digit needs to be added to the end of the cutter (variable). The digit cannot be 1 or 0 (as they can be confused with lower case L or capital O); usually it starts at 5, because any further duplicates result in changing that 5 to a different number such that the authors are sorted alphabetically. So if the next pass of checking results in a duplicate, then the authors need to be compared, and the 5 changed to a 3 if the author comes first alphabetically (compared to the value in column G) or to an 8 if it comes second alphabetically. After another round of checking, the number is changed again if duplicated, and again following alphabetical sequence. After this, if there's still a duplicate, then the macro should continue to run. At the end of the code, when all the variables are saved into the last row, a note should be added in column O stating that the cutter duplicates row x (wherever the last duplicate occurred).
Case 2:
The variable wkMark (work mark) is created in the code and it initially has the value of the first letter of the title of the book; it eventually gets stored in column D. The wkMark and cutter are merged in the variable cutWrk (i.e.
cutWrk = cutter & wkMark, with no space in between). Now the cutWrk needs to be compared to the mergers of columns C & D of the extant records. If there's a duplicate, then the wkMark takes the first two letters of the title (and cutWrk is redefined), and it checks again. If there's another duplicate, then it compares the input variable year to the value in column I. If the years are different, then
cutWrk = cutWrk & " " & year.If they are the same, then we're back to the added digit just like in case 1, starting with a 5, and then adjusting the digit up or down to sort alphabetically in case of additional duplicates. This digit gets stored in the variable cutApp and results in:
cutWrk = cutWrk & "." & cutApp & " " & year.The main thing I can't figure out is how to modify a variable in different ways after each pass of checking for duplicates, since it's always conditional on the number of passes and in what ways the duplicates occur. I'm still rather novice at VBA coding, so I'm grateful for any help you can provide. Thanks in advance. Cheers.
