+ Reply to Thread
Results 1 to 6 of 6

Need Help With an Array

  1. #1
    Registered User
    Join Date
    08-02-2019
    Location
    Universal City. Texas
    MS-Off Ver
    7
    Posts
    3

    Need Help With an Array

    Can someone suggest a simple routine routine to do the following with an array.

    The array is 10x12. Each cell in the array has a letter in it.
    What I would like to do is type in a letter, F for example. Then every cell which contained F will highlight. I would repeat with more letters and they would highlight also. Each letter would be highlighted with a different color.

    I don't know visual basic so I am hoping I can do this with regular excel formulas.

    I don't need prompts or any dialog. I am good with just typing info into a cell.

    I know simple stuff on excel and have used a few formulas but this particular thing I don't know how to do.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,703

    Re: Need Help With an Array

    Welcome to the Forum jamesq!

    If each cell only has one letter, then you can do this with conditional formatting. Where do you want to "type in a letter"? And where you "repeat with more letters"? Are you putting all these letters in one cell as string of letters, or each letter in a different cell?

    Here is a quick demo. This uses three conditional formatting rules to find all cells that match each of the first three characters of a string in the lower right cell. You need one rule for each color.
    Attached Files Attached Files
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    08-02-2019
    Location
    Universal City. Texas
    MS-Off Ver
    7
    Posts
    3

    Re: Need Help With an Array

    I really didn't think much about exactly where to type in a variable.
    I didn't mention what this is for but for clarification, it might help

    What the array represents is the neck of a 10 string steel guitar. There are 12 frets, so the array is 10x12. The letter typed in is a note. The highlights would show every place on the neck where that note can be played. Sometimes more than one note would be typed in and they would all need to be highlighted at the same time but each with a different color. I appreciate the help. I need to sign off for the night.

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,703

    Re: Need Help With an Array

    I can set that up for you. What tuning do you use? I am getting on a plane in two hours, so if I can't resolve it for you by then hopefully someone else can help.

    You are going to have the notes A-G but also enharmonic accidentals (i.e., the same string/fret is going to be both G# and Ab). My solution is designed to work only with one letter per square, and you are going to need two characters per square. How will you notate accidentals in your grid?

    What is the max number of notes to enter at one time? Four-note seventh chords (Maj7/min7/7/dim7/m7b5)? Or will you be adding extensions/tensions?
    Last edited by 6StringJazzer; 08-22-2019 at 08:28 PM.

  5. #5
    Registered User
    Join Date
    08-02-2019
    Location
    Universal City. Texas
    MS-Off Ver
    7
    Posts
    3

    Re: Need Help With an Array

    What you already sent works just fine. There needs to be 1 more column for open strings.
    Then there needs to be a way to enter notes with sharps and flats.
    I replied yesterday but for some reason that doesn't show up on the thread.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,460

    Re: Need Help With an Array

    Posts are never hidden or in moderation, so you simply can't have saved the post, which is why it's not showing.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Filter Array 1 with Array 2 as criteria, returning filtered data to source sheet
    By nigelog in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-31-2019, 01:08 PM
  2. Replies: 5
    Last Post: 02-22-2018, 01:47 AM
  3. Replies: 1
    Last Post: 01-04-2018, 01:07 PM
  4. [SOLVED] Array formulae with different sized array, e.g. array-if() or somproduct-if()?
    By hlhans in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-09-2016, 03:59 PM
  5. [SOLVED] Populate one array from another array and print new array as a range
    By Kaden265 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-07-2014, 07:52 AM
  6. [SOLVED] Quick Array question - Copy array to another array then resize?
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-02-2013, 01:17 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1