Ask Experts Questions for FREE Help !
Ask
    dannac's Avatar
    dannac Posts: 267, Reputation: 9
    Full Member
     
    #1

    Jul 27, 2014, 12:21 PM
    Need VBA beyond my knowledge
    Excel 2003
    Workbook with sheets "Search, A, B, C, etc (through alphabet)
    Sheets a, b, c, etc have a song name in col A, and a letter code in col B.


    Clicking on "search" sheet opens a userform with radio buttons.
    Clicking a radio button puts a letter code value into cell D3 on the "search" sheet.


    I have the above set up and will attach the file.
    ---------------------------------------------------------------------


    VBA would search sheets (a, b, c, etc) col B for the matching code from "search" sheet cell D3.
    Copy the song names with matching code to col A on "search" sheet.


    When a different radio button is selected, it would clear the previous copied song names
    and serach for the new matching code, and copy those songs to the "search" sheet.
    Attached Files
  1. File Type: xls ChurchSongs.xls (89.5 KB, 114 views)
  2. ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #2

    Jul 27, 2014, 12:45 PM
    Just a point, Excel is not a database. It can be used to track simple data, but it sounds like you have exceeded the capabilities of Excel in easily tracking data. While I believe you can do what you need using VBA, you can do what you want in Access (a real database) with little if any coding.
    dannac's Avatar
    dannac Posts: 267, Reputation: 9
    Full Member
     
    #3

    Jul 28, 2014, 09:31 AM
    I do have Access... looked at it once and was rather confused.
    Much rather excel but may have to give it another look.

    Thanks for the reply.
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #4

    Jul 28, 2014, 09:43 AM
    Access is not an advanced version of Excel. And if you try to look at it that way, you will be confused.

    I took a look at your spreadsheet, So all you have is a single table with title and code. (In Access you would create a lookup table for the codes).

    You could use a multi-value field to select as many songs as you want, then print out a report of the selected songs.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #5

    Jul 28, 2014, 05:19 PM
    I've added the TITLES to every sheet to make the filtering of data clean and simple. Then built a macro into the SEARCH sheet module that will watch for any change in cell D3. If you edit that cell, all the rows from all the sheets with a matching column B value will be collected.
    Attached Files
  3. File Type: xls ChurchSongs.xls (117.0 KB, 159 views)
  4. dannac's Avatar
    dannac Posts: 267, Reputation: 9
    Full Member
     
    #6

    Jul 28, 2014, 06:42 PM
    JB... thanks for the file.

    Not working as I expected though. It seems to bring all songs in and filters/sorts.

    Only wanted to bring songs that match the key code put in D3 by the radio button selection.

    If I click "Prelude"... only songs that have the key of "P" will be copied to the Search sheet.

    Looked at the code, but did not understand it enough to try and fix.

    Thanks
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #7

    Jul 30, 2014, 01:09 PM
    When I click on the Prelude in your form, it puts a P in D3 which causes all the "P" rows to be copied in from all the other sheets. How is that not what you wanted?
    dannac's Avatar
    dannac Posts: 267, Reputation: 9
    Full Member
     
    #8

    Jul 31, 2014, 02:01 AM
    Not sure what's going on JB... it does not work that way on my computer.

    Does not matter which radio button is selected, it brings all songs in.




    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #9

    Jul 31, 2014, 07:50 AM
    Well, that's just odd. I just opened the same file above, working perfectly.

    Video: 2014-07-31_0749 - JerryBeaucaire's library
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #10

    Jul 31, 2014, 08:03 AM
    Just shot in the dark silly suggestions, maybe edit this one line of code:

    Code:
    ws.UsedRange.AutoFilter 2, Range("D3").Value
    Attached Files
  5. File Type: xls ChurchSongs.xls (116.5 KB, 99 views)
  6. dannac's Avatar
    dannac Posts: 267, Reputation: 9
    Full Member
     
    #11

    Aug 1, 2014, 03:29 AM
    Hah... shot in the dark suggestion fixed it. Thanks JB

    Home

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

VBA If Statement [ 4 Answers ]

Ok. Here's my problem. I have a spreadsheet that I use which filters on 6 different filter criteria and copies the result set of each filter criteria to a separate worksheet. However, on occasion there are no results found for a given criteria. What I want to do is when no results are found...

Can VBA do this for me? [ 7 Answers ]

Refer attachment; For sheet "SPPA" cell "D18" value to be recorded on sheet "HO plan" cell "T7", and this is where it gets nasty, as the date rolls over on the "SPPA" sheet cell "F1", so must the macro record the new value in the next cell for sheet "HO plan", ie: If "SPPA" sheet cell "F1"...

Can this be done using VBA? [ 5 Answers ]

Ok - I have a spreadsheet that is exported from an application I use at work. It formats the data in a manner that makes it really difficult to manipulate but I think I've worked out a solution that gets me what I need; only it is very tedious. The data starts in row 16 of the sheet. There is...

Is organizational knowledge basically equivalent to the knowledge possessed by the in [ 1 Answers ]

Is organizational knowledge basically equivalent to the knowledge possessed by the individuals in the organization?


View more questions Search