+ Reply to Thread
Results 1 to 22 of 22

Rows.Count error in dynamic range naming

  1. #1
    Forum Contributor
    Join Date
    05-20-2008
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    144

    Rows.Count error in dynamic range naming

    Please help.

    The attached workbook is designed to be used by my receptionists as and when required.

    My problem is getting the dynamic ranges 'RCL' & 'LCL' to be accurate.

    At the moment I have tried one method to name 'RCL' but that only gives me one line, and another method for 'LCL' which gives me all my options but then includes thousands of blank lines.

    I have included the code below to save anyone trawling through everything.
    This is my first project proper with VBA so am learning as I go along.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Richard Schollar's Avatar
    Join Date
    05-23-2006
    Location
    Hampshire UK
    MS-Off Ver
    Excel 2002
    Posts
    1,264

    Re: Rows.Count error in dynamic range naming

    Is this code located in a sheet module? If so, your problems are probably caused by not fully qualifying your range references with the sheets on which the ranges appear.
    Richard Schollar
    Microsoft MVP - Excel

  3. #3
    Forum Contributor
    Join Date
    05-20-2008
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    144

    Re: Rows.Count error in dynamic range naming

    That is some of the code from the userform I am using.

    Upon clicking on 'Search' data is advance filtered and copied to the worksheet 'RCL'.
    It then sets the dynamic range and calls it 'RCL' (I now see that I should rename one of these to make it clearer.)

    At least that was the idea. I appreciate you looking at it but is there any chance you could expand a bit more on what the solution to my problem seems to be.

  4. #4
    Forum Contributor
    Join Date
    05-20-2008
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    144

    Re: Rows.Count error in dynamic range naming

    OK, so the piece of code I used before now seems to be working on my home PC.

    Please Login or Register  to view this content.
    The only problem being that if they choose an option within the program that gives a fairly short list, if they then change it to an option that would give more results, it doesn't expand the list.

    I was trying to write the Rows.Count into the code at a point after the filtered results had been copied through so that the number of rows would be counted fresh each time. This doesn't seem to be happening and I have no idea on how to sort it.

    Help, please......

  5. #5
    Forum Contributor
    Join Date
    05-20-2008
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    144

    Re: Rows.Count error in dynamic range naming

    No ideas?

    Am out of my depth on this one.

  6. #6
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Rows.Count error in dynamic range naming

    Here is amended code, untested, modified based on what I think you are doing.
    You need to turn on the option in VB editor to force proper declaration of variables. You had at least one undeclared variable (LR). Tools > Options > Editor tab > check the box "Require variable declaration"

    Your dynamic range selections were incorrect and were selecting the entire column.
    In VBA, it is generally not necessary to "Select" a sheet in order to act upon it. Just be sure to fully qualify the sheet reference. Compare change below to your original code.

    I've left the last "Select" statement at the bottom of the code, but you could use this bit of code in its place:

    Please Login or Register  to view this content.

    Amended Code:

    Please Login or Register  to view this content.
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  7. #7
    Forum Contributor
    Join Date
    05-20-2008
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    144

    Re: Rows.Count error in dynamic range naming

    Thank-you for tidying up my code, it looks a lot neater your way.

    Please Login or Register  to view this content.
    This unfortunately is only reading the first row even though there is always more than 1 row copied across. Any ideas on that?

  8. #8
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Rows.Count error in dynamic range naming

    That line of code doesn't give me any problems. However, here is another way.

    Please Login or Register  to view this content.
    Last edited by Palmetto; 04-03-2010 at 02:41 PM.

  9. #9
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,669

    Re: Rows.Count error in dynamic range naming

    Palmetto, you have an unqualified range reference.
    Entia non sunt multiplicanda sine necessitate

  10. #10
    Forum Contributor
    Join Date
    05-20-2008
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    144

    Re: Rows.Count error in dynamic range naming

    I have gone back to using the following formula in the named ranges.

    Please Login or Register  to view this content.
    This has stopped the problem of only get one entry returned.
    It also returns the exact number of rows on the first 'search' but on subsequent searches, is returning the same number of rows as the first search even if the new search should return more or less options.

    Is the dynamic range not meant to automatically adjust to any changes in the sheet?
    Is the problem because I am copy/pasting onto the sheet?

    Is there a way to use the above code within the VBA code attached to the 'Search' button to make it re-evaluate how many rows are required?

    I don't know why your solution is working for you and not for me but I have checked it on various PC's and they are all the same. Have you closed the userform and re-run?

  11. #11
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Rows.Count error in dynamic range naming

    Don't bind your controls to the range directly using the RowSource property. Instead, use the code that names the ranges to assign the list to the comboboxes:
    Please Login or Register  to view this content.
    for example.
    Remember what the dormouse said
    Feed your head

  12. #12
    Forum Contributor
    Join Date
    05-20-2008
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    144

    Re: Rows.Count error in dynamic range naming

    OK, I have deleted out from the RowSource field any references to any ranges.

    Where should I be putting your code romperstomper? I am also presuming that I substitute "blah" for the name of the sheet that the range is found, am I right?

    Thanks for all the help from everyone so far.

  13. #13
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Rows.Count error in dynamic range naming

    That's correct. Put the code at the end of the routine that creates the new named ranges.

  14. #14
    Forum Contributor
    Join Date
    05-20-2008
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    144

    Re: Rows.Count error in dynamic range naming

    Please Login or Register  to view this content.
    Hmmm, I'm still not getting the desired results.
    I am sure I have put it in the right place but it doesn't like it and errors.

    When I say I am sure I have done it right I obviously mean that I am not sure at all! :D
    Last edited by Andrew-Mark; 04-06-2010 at 10:02 AM.

  15. #15
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Rows.Count error in dynamic range naming

    What error and where?
    I'd also point out that there is no need to do an advanced filter in place and then copy to another sheet - advanced filter has the option to output the filtered data to another sheet directly.

  16. #16
    Forum Contributor
    Join Date
    05-20-2008
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    144

    Re: Rows.Count error in dynamic range naming

    The error is:

    Run-time error '381'
    Could not set the list property. Invalid property array index.

    I have marked in the code box where the de-bugger takes me.

  17. #17
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Rows.Count error in dynamic range naming

    What address is your RCL range pointing at? Is it only one cell?

  18. #18
    Forum Contributor
    Join Date
    05-20-2008
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    144

    Re: Rows.Count error in dynamic range naming

    The range RCL is a dynamic list on the worksheet 'RCL' and changes as the search parameters are changed. It could be anything from one cell to 40 cells.

  19. #19
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Rows.Count error in dynamic range naming

    If it's one cell, then you can't use the List property. Try this:
    Please Login or Register  to view this content.

  20. #20
    Forum Contributor
    Join Date
    05-20-2008
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    144

    Re: Rows.Count error in dynamic range naming

    This is really weird.

    I substituted in your new code and thought 'Hallelujah!', it worked!!
    Then I changed one of the search parameters and searched again.
    The list imported was reduced to one item only for each box. There should have been multiple for both.

    I have re-attached the workbook so you can see what happens now.

    It is only the CL Criteria section that seems to be causing problems.
    I am selecting;
    'Soft' for both R&L
    'Monthly' for R&L
    'Spherical' for R, 'Toric' for L
    'Single Vision' for R&L

    This first search brings me my lists in for both the lens selection boxes.
    If I now change the 'Toric' to 'Spherical' it no longer works properly.

    Aaaaarrrrggghhhh!
    Attached Files Attached Files

  21. #21
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,669

    Re: Rows.Count error in dynamic range naming

    The list range has to be contiguous, I think. So you need to do a For Each loop and add each cell value separately to the list.

  22. #22
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Rows.Count error in dynamic range naming

    This version seems to work for me:
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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