Re: To Ian Thanks!

You have a good nap down there. I guess you are getting ready for summer
pretty soon and we will be getting our usual dose of 4 to 5 months of ice
and snow.
Anyway, I will be waiting to go to that link and check out that spread***
as soon as you get it done.
Ross
"Ken Johnson" <KenCJohnson@xxxxxxxxx> wrote in message
news:1127770148.209203.262420@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> Ross,
> I'm in Sydney, Australia, which I guess puts us on opposite sides of
> the Earth, explaining the gaps in our communications. I would normally
> be asleep right now (5:30 am) but my daughter had to be up early to go
> to a Duke of Edinburgh excursion so I thought I'd just check my Google
> Groups.
> I finished the transposing macros late last night and I'm happy with
> the way it works.
> Clicking the up arrow runs a single line macro that makes an integer
> variable called intDirection equal to 1, which it then passes on to the
> Transpose macro.
> Clicking the down arrow runs another single line macro that makes
> intDirection equal to -1, which is then passed on to the Transpose
> macro.
> The Transpose macro has intDirection declared inside the brackets after
> its name:
> Private Sub Transpose (intDirection As Integer)
> so that it can receive and use the value of intDirection.
> The Transpose macro basically does the following:
> 1.Set up a string array variable with fourteen rows and two columns of
> chord key names:
> Ab,empty string
> A,A
> Bb,A#
> B,Cb
> C,C
> C#,Db
> D,D
> Eb,D#
> E,Fb
> F,E#
> F#,Gb
> G,G
> Ab,G#
> A,empty string
>
> 2.Locate a cell in the range A1:Z100 with text using the SpecialCells
> Method. This is definitely the fastest way to do it.
>
> 3.Test the cell for Bold format. If True the cell contains a chord.
>
> 4.Make a string variable of the chord's key name by Parsing the chord
> name, eg C#Maj7 results in C#. Also, make a string variable of the
> chord type eg Maj7.
>
> 5.Search the array of chord key names for the chord key name. This
> search starts on the second row of the array. The search sequence is
> R2C1 then R2C2 then R3C1 then R3C2 then R4C1 then R4C2 etc up to R13C2.
> This covers all the possible combinations of A to G with and without #
> or b. The extra row at the top and bottom of the array is only used
> when an A is transposed to an Ab or an Ab is transposed to an A, which
> occurs later in the code.
>
> 6.If a chord found in A1:Z100 has a key name that is not found in the
> array the offending cell is selected, a message pops up stating that
> there is something wrong with the chord in the selected cell and the
> macro is aborted. The user then has to fix up the problem before
> retrying the transpose.
> NB so far no changes have been made to the work*** so we don't end up
> with some chords transposed and others not, which would be disasterous!
>
> 7.If no problems were encountered by the code it repeats all the above
> steps but, when a match between a cell's chord key name and a chord key
> name in the array is found the chord key name is changed to the
> previous or next chord key name in column 1 of the array. Whether it is
> previous or next is determined by the value of intDirection, which
> depends on which arrow was clicked.
>
> 8.Combine the new chord key name with the original chord type and
> replace the cell with this new value.
>
> This all happens in a short time thanks to the speed of the
> SpecialCells method.
>
> I'm thinking of adding one extra feature:
> What if the user is not happy with say F# (my preference) and would
> prefer the enharmonic Gb. I would like the user to be able to select
> one of offending chords then click a button that runs a macro that
> changes all the instances of F# to Gb. Should be easy. I'll try it
> later today.
>
> It's now 7:15 am and I'm on holiday so I'm going back to bed. I won't
> post the code because its proper function depends on the set up of the
> work***.
> I'll put the workbook on the web. However, that's something I've never
> done before and I will need my son's help to do that. He too is on
> holiday and rarely rises before noon.
> I'll keep you posted.
> Ken Johnson
>


.

Loading