+ Reply to Thread
Results 1 to 11 of 11

vba Array needs help - how to preserve and use Ubound(value) ouside of a Loop

  1. #1
    Forum Contributor
    Join Date
    10-22-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    114

    vba Array needs help - how to preserve and use Ubound(value) ouside of a Loop

    Hi - Thanks for looking into this. Any help and comments would be greatly appreciated!

    Here my code:

    Please Login or Register  to view this content.
    The code stops at the last line with an error msg: Type missmatch
    I wanted to use the Ubound(newArr) value

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: vba Array needs help - how to preserve and use Ubound(value) ouside of a Loop

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    10-22-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    114

    Re: vba Array needs help - how to preserve and use Ubound(value) ouside of a Loop

    AB33 - Thanks for your help. It works great.

    Would you mind explain why my last line of code doesn't work : Cells(1, "D").Resize(UBound(newArr), 1) = newArr and how come it work in you code?

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: vba Array needs help - how to preserve and use Ubound(value) ouside of a Loop

    Please Login or Register  to view this content.
    newArr has not been dimensioned. You are trying to write back to newArr in to a range, what is newArr? Where are the first (row) and second (column) dimensions?
    This works too.

    Please Login or Register  to view this content.

  5. #5
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: vba Array needs help - how to preserve and use Ubound(value) ouside of a Loop

    newArr was not an array in your code-the Redim statement in AB33's code made it an array
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  6. #6
    Forum Contributor
    Join Date
    10-22-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    114

    Re: vba Array needs help - how to preserve and use Ubound(value) ouside of a Loop

    Appreciate the explanation. Just one more question on this line: ReDim newArr(1 To UBound(arr, 1), 1 To 1)
    What does ....,1 to 1) mean? Is it the second dimension of newArr?

  7. #7
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: vba Array needs help - how to preserve and use Ubound(value) ouside of a Loop

    Yes it is, you could have written like

    Please Login or Register  to view this content.
    But this is overkill and can create memory run time error. You only need a single column to return, so no need to go to the end of columns.

  8. #8
    Forum Contributor
    Join Date
    10-22-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    114

    Re: vba Array needs help - how to preserve and use Ubound(value) ouside of a Loop

    Thanks again. I think I got it.

  9. #9
    Forum Contributor
    Join Date
    10-22-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    114

    Re: vba Array needs help - how to preserve and use Ubound(value) ouside of a Loop

    I was playing around with it, and this also works:
    Since the newArr is not dimensioned here and it works. I don't know why?


    Please Login or Register  to view this content.

  10. #10
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: vba Array needs help - how to preserve and use Ubound(value) ouside of a Loop

    it only works because all your values in the array are the same. the last line is actually putting one value in all the cells and not populating them with an array

  11. #11
    Forum Contributor
    Join Date
    10-22-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    114

    Re: vba Array needs help - how to preserve and use Ubound(value) ouside of a Loop

    Oh, I see. Thanks for explanation.

+ 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] Type-Mismatch on UBOUND(Array)
    By codeslizer in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-17-2013, 06:03 AM
  2. [SOLVED] which value UBound will e returns of the array ?
    By nur2544 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-25-2013, 12:07 AM
  3. Array Ubound
    By gti_jobert in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-27-2006, 05:40 PM
  4. UBound of multi-dimensional array?
    By paulharvey in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-16-2005, 02:05 PM
  5. [SOLVED] dif Ubound for member array when debugging
    By Eric in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-28-2005, 04:05 PM

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