#1
|
||||
|
||||
Conflict between Dim, ReDim and Split
I'm not having much luck getting answers to my questions here recently. Weird set of questions, maybe. Here's another one, a problem I ran into today while attempting something I thought would be pretty conventional:
Stage 1: I calculate the number of rows and columns I need, populate the array and then dump it into a worksheet. This works: Code:
Dim ar() ReDim ar(1 to rZ, 1 to cZ) 'blah, blah, blah Range(ows.Cells(1, 1), ows.Cells(rZ, cZ)).Value = ar Code:
ar = Split(vm) Didn't work. When I remove the Dim statement, the VBA interpreter complains at "compile" time that the ReDim statement is invalid. Ok, apparently I still need "Dim ar()", it just needs to be above the first use of ar. So (Stage 3) now my program has the statements in this order (though not all together): Code:
Dim ar() ar = Split(vm) ReDim ar(1 to rZ, 1 to cZ) 'blah, blah, blah Range(ows.Cells(1, 1), ows.Cells(rZ, cZ)).Value = ar I'll have to use a different variable name, it appears. But why? I looked up Split and ReDim in the language reference and don't see anything there to indicate where the limitation comes in. Does anyone know? |
#2
|
||||
|
||||
You've got this post marked as solved - perhaps that is why you aren't getting answers.
As far as I know, you use Split to create a one dimensional array of substrings from a string by supplying the string and the character used to separate the substrings. Therefore your commands to dim and populate the array can only populate one row or column and might look like Code:
Sub aTest() Dim ar() As String, vm As String vm = "a,b,c,d,e,f" ar = Split(vm, ",") Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(1, UBound(ar) + 1)).Value = ar End Sub
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#3
|
||||
|
||||
Cross-posted at: Conflicts between Dim, ReDim and Split | Chandoo.org Excel Forums - Become Awesome in Excel
For cross-posting etiquette, please read: Excelguru Help Site - A message to forum cross posters
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#4
|
||||
|
||||
Partly right. First I use Split to turn a string of space-delimited words into an array; I needed the array for only a few lines, one "paragraph", so I used a throwaway variable:
Code:
' Let's say vm contains "AR GA MC". Then: ar = Split(vm) ' Now ar(0) contains "AR", ar(1)="GA", ar(2)="MC" Code:
ReDim ar(1 to rZ, 1 to cZ) 'max row and column For jr = 2 to rZ ar(jr, 1) = Column1Value ar(jr, 2) = Column2Value ' ...and so on Next jr Range(ows.Cells(1, 1), ows.Cells(rZ, cZ)).Value = ar After the question had languished here for a while, I gave up and posted it over at another forum. After I started getting answers over there I marked this one "solved" so as not to waste anyone's time here. Eventually someone there got me to try declaring ar() As String, and it worked! Still not sure why letting it stay Variant didn't work as well, but I'll keep it in mind. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Mail Merge - split merged documents and rename each split document based on text in header | FuriousD | Word VBA | 1 | 05-12-2019 04:06 AM |
Split word file into several PDF using the bookmarks as split positions and name | Fixxxer | Word VBA | 7 | 10-08-2018 01:10 AM |
Split function in Excel (split the screen) | Officer_Bierschnitt | Excel | 1 | 07-05-2017 07:02 AM |
Folders in your mailbox have name conflict | baclava | Outlook | 0 | 10-19-2016 08:50 AM |
Template conflict | b0x4it | Word | 0 | 05-21-2011 07:44 PM |