Array with many cells from a row but not in order or together

Discussion in 'Excel Programming' started by L. Howard, May 2, 2014.

  1. L. Howard

    L. Howard Guest

    This works okay for a few cells when aName is found in aRng and returns four cells to a destination.

    aName = InputBox("Enter a name.", "Name Information")

    For Each c In aRng
    If c = aName Then
    c.Copy Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp)(2)
    c.Offset(, 1).Copy Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp)(2)
    c.Offset(, 4).Copy Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp)(2)
    c.Offset(, 8).Copy Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp)(2)
    End If
    Next

    What I have now is when aName is found in aRng then I need several cells in that row returned and transposed to a destination column.

    Also, the cells to return are not in order or together.

    Say for whatever row aName is in I need cells in columns in this order: C, F, P, S, D&E, R, M, Y, AA, BM. (Example only)

    Notice D & E need to be returned to the same destination cell.

    I am thinking an array method but am lost on not only on how to nail down the row aName is in but how to reference all the cells by column in that particular row.

    Thanks.
    Howard
     
    L. Howard, May 2, 2014
    #1
    1. Advertisements

  2. L. Howard

    GS Guest

    > This works okay for a few cells when aName is found in aRng and
    > returns four cells to a destination.
    >
    > aName = InputBox("Enter a name.", "Name Information")
    >
    > For Each c In aRng
    > If c = aName Then
    > c.Copy Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp)(2)
    > c.Offset(, 1).Copy Sheets("Sheet2").Range("B" &
    > Rows.Count).End(xlUp)(2) c.Offset(, 4).Copy
    > Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp)(2) c.Offset(,
    > 8).Copy Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp)(2) End
    > If Next
    >
    > What I have now is when aName is found in aRng then I need several
    > cells in that row returned and transposed to a destination column.
    >
    > Also, the cells to return are not in order or together.
    >
    > Say for whatever row aName is in I need cells in columns in this
    > order: C, F, P, S, D&E, R, M, Y, AA, BM. (Example only)
    >
    > Notice D & E need to be returned to the same destination cell.


    Are you joining this data from both cells into a target cell?

    OR

    Are you putting the same value into both those cells?

    OR

    Are those cells merged?
    >
    > I am thinking an array method but am lost on not only on how to nail
    > down the row aName is in but how to reference all the cells by column
    > in that particular row.
    >
    > Thanks.
    > Howard


    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, May 2, 2014
    #2
    1. Advertisements

  3. L. Howard

    L. Howard Guest


    > > order: C, F, P, S, D&E, R, M, Y, AA, BM. (Example only)

    >
    > Are you joining this data from both cells into a target cell?


    The eleven source cell examples would be in ten separate destination cells (D & E source cells into a single destination cell too, a space would probably make sense)

    No merged cells! Hiss<g>

    Howard
     
    L. Howard, May 2, 2014
    #3
  4. L. Howard

    GS Guest

    >
    >>> order: C, F, P, S, D&E, R, M, Y, AA, BM. (Example only)

    >>
    >> Are you joining this data from both cells into a target cell?

    >
    > The eleven source cell examples would be in ten separate destination
    > cells (D & E source cells into a single destination cell too, a space
    > would probably make sense)


    Okay then, I have a better understanding of what you're trying to do.

    Normally I'd use a delimited constant to store the source/target range
    refs like this...

    Const sCopyFromCols$ = "C,F,P,S,D:E,R,M,Y,AA,BM"

    ...which would have a matching 'sCopyToCols$' with the target col
    labels. Note that the 5th element in this string uses ":" to delimit
    its content's start:end as would also be used in a range address. This
    will need to be checked for in your loop so it gets handled correctly.
    If, as you say, this element gets combined into a single cell then that
    cell's col label needs to occupy the corresponding position in
    'sCopyToCols'. So using 'dummy' labels...

    Const sCopyToCols$ = "E,F,C,D,G,M,T,Z,AC,BK"

    ...which will put the data 'from' cols D:E 'to' col G on the target
    sheet.

    <FYI>
    I find this technique most useful for code maintenance, as well as loop
    management! No doubt you've seen similar examples from me before and so
    I urge you to review any samples you've archived.
    </FYI>

    I normally use a temp variant (vTmp) to Split() internal delimited
    strings into useable elements. In this case, perhaps something like...

    Dim vSrcCols, vTgtCols, vTmp, n&

    vSrcCols = Split(sCopyFromCols, ",")
    vTgtCols = Split(sCopyToCols, ",")

    For n = LBound(vSrcCols) To UBound(vSrcCols)
    vTmp = Split(vSrcCols(n), ":")
    If Not LBound(vTmp) = UBound(vTmp) Then
    'process as a range
    Else
    'process as a single cell
    End If
    Next 'n

    ...where vTmp is ALWAYS used when you know you have a range to work
    with. As to how to manage putting the data into a single cell...

    rngTgt = Join(vTmp)
    OR
    rngTgt = Join(vTmp, " ")

    ...where the 1st line puts a space by default, and the 2nd line
    specifies the delimiter to use.

    I find it most helpful to use a consistent methodology for working with
    data and parsing it into arrays. The examples I've shown here are from
    'working' scenarios in my own projects and so I can afirm that they
    work correctly for the 'solution contexts' they are applied to.

    What makes this possible is that I have devised
    the coding methodology around my understanding of it.

    You'll appreciate the value of the preceeding comment when you
    establish your own consistent methodologies for 'soution contexts'! Be
    patient!!

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, May 2, 2014
    #4
  5. L. Howard

    L. Howard Guest

    Hmm, looks daunting. I will give it a shot. May cry UNCLE.

    This I understand will put D:E into G because both are the fifth element in the array.

    <Const sCopyFromCols$ = "C,F,P,S,D:E,R,M,Y,AA,BM"

    <Const sCopyToCols$ = "E,F,C,D,G,M,T,Z,AC,BK"

    <..which will put the data 'from' cols D:E 'to' col G on the target
    sheet

    Thanks.
    Howard
     
    L. Howard, May 2, 2014
    #5
  6. L. Howard

    L. Howard Guest

    Just to add, Does not look like the out put will be transposed to a column.

    This will come from a row:
    Const sCopyFromCols$ = "C,F,P,S,D:E,R,M,Y,AA,BM"

    This will need to be a single column:
    Const sCopyToCols$ = "E,F,C,D,G,M,T,Z,AC,BK"

    Truly confused before I begin as usual.

    Howard
     
    L. Howard, May 2, 2014
    #6
  7. L. Howard

    GS Guest

    > Just to add, Does not look like the out put will be transposed to a
    > column.
    >
    > This will come from a row:
    > Const sCopyFromCols$ = "C,F,P,S,D:E,R,M,Y,AA,BM"
    >
    > This will need to be a single column:
    > Const sCopyToCols$ = "E,F,C,D,G,M,T,Z,AC,BK"
    >
    > Truly confused before I begin as usual.
    >
    > Howard


    A 1D zero-based array only has 1 row with UBound+1 cols, and so...

    Range("G" & k).Resize(UBound(MyArray) + 1, 1) = _
    Application.Transpose(MyArray)

    ...to put the data into as many contiguous cols as specified in
    Resize(). To populate a row with a 1D array...

    Range("G" & k).Resize(1, UBound(MyArray) + 1) = MyArray

    ...to put the data into as many contiguous rows as specified in
    Resize().

    The rule is 'put UBound+1 in the correct arg position of Resize(),
    transposing if in 1st position'! It's easy to remember if you think of
    a 1D array's orientation as 'landscape' by default and so needs to be
    transposed to 'portrait' when desired.<g>

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, May 2, 2014
    #7
  8. L. Howard

    L. Howard Guest

    Caution, code under construction by me.

    How do I set the columns I want to vSrcCols once c has found the Input box ID number in aRng?

    Sub myJQsheet()

    Dim vSrcCols, vTgtCols, vTmp, n&
    Dim vSrcCols As String
    Dim Lrow As Long
    Dim aRng As Range
    Dim c As Range

    Lrow = Sheets("JQ5027-Session1-Apr 23 17-32-03").Cells(Rows.Count, "A").End(xlUp).Row
    aRng = Sheets("JQ5027-Session1-Apr 23 17-32-03").Range("A1:A" & Lrow)

    vSrcCols = Split(sCopyFromCols, ",")
    vTgtCols = Split(sCopyToCols, ",")

    vSrcCols = InputBox("Enter an ID number.", "ID Search")
    If vSrcCols = vbNullString Then Exit Sub

    For Each c In aRng
    If c = vSrcCols Then
    vSrcCols = "C", "F", "P", "S", "D"&"E", "R", "M", "Y", "AA", "BM") ?????


    Next

    For n = LBound(vSrcCols) To UBound(vSrcCols)
    vTmp = Split(vSrcCols(n), ":")
    If Not LBound(vTmp) = UBound(vTmp) Then

    '/** this needs to start at C4
    Range("C" & k).Resize(UBound(MyArray) + 1, 1) = _
    Application.Transpose(MyArray)

    'process as a range
    Else
    'process as a single cell
    End If
    Next 'n

    '..where vTmp is ALWAYS used when you know you have a range to work
    'with. As to how to manage putting the data into a single cell...

    rngTgt = Join(vTmp)
    'OR
    rngTgt = Join(vTmp, " ")

    '..where the 1st line puts a space by default, and the 2nd line
    'specifies the delimiter to use.
    End Sub
     
    L. Howard, May 2, 2014
    #8
  9. L. Howard

    GS Guest

    You need to enter your InputBox values same as they appear in the
    constants, which is a delimited list. So when the user types...

    C,F,P,S,D:E,R,M,Y,AA,BM

    ...into the InputBox, intellisense shows it as a string same as exampled
    in the constants.

    You need to change the name of the var receiving the InputBox value
    because, as shown, it's a duplicate declaration...

    Dim vSrcCols, vTgtCols, vTmp, n&
    Dim vSrcCols As String

    ...where the 1st line types the 1st 3 vars as 'Variant', hence the type
    prefix "v" in the name. Var n is type 'Long' as indicated by the 'type
    symbol' appended to the name.

    The 2nd line re-declares vSrcCols as type 'String' and so this sub
    won't even run because VBA will throw an exception (a.k.a raise an
    error). @nd point is its prefix suggests its type is 'Variant' same as
    those in the 1st line. I normally use a 'Variant' for prompted
    responses because the return type can vary depending on the vehicle
    used. What's consistent about this approach is how I understand my code
    EVERY TIME I prompt for user input...

    Dim vRet, vAns
    vRet = InputBox...
    ..followed by use-appropriate validation code

    vAns = MsgBox...
    ..followed by use-appropriate validation code

    Since you're using VBA's InputBox() you're forcing VBA to coerse what
    gets entered to type 'String'. I suggest you use Excel's InputBox() so
    you can specify data type and save yourself the extra validation (not
    apparent here!) coding required to make sure it's good type.

    Finally, it's a good idea to write down in plain english what the
    'intended' logic of your code is. That will cue you as to how to write
    the code. Note also that since BOTH source/target col label lists are
    'in syn', you can manage them both in the same For..Next loop because
    their indexes exactly match.

    This next bit of code...


    '/** this needs to start at C4
    Range("C" & k).Resize(UBound(MyArray) + 1, 1) = _
    Application.Transpose(MyArray)

    ...is in the wrong place because:
    it's 'output' code

    AND if vTmp is an array then it needs to process source data
    as a range.

    This is where you will join the contents of D:E for
    output to G in the target sheet...

    With wksSource
    For n = LBound(vSrcCols) To UBound(vSrcCols)
    vTmp = Split(vSrcCols(n), ":")
    If IsArray(vTmp) Then
    vDataOut = .Range(vTmp(0) & k).Value & " " _
    & .Range(vTmp(1) & k).Value
    Else
    vDataOut = .Range(vTmp & k).Value
    End If

    wksTarget.Range(vTgtCols(n) & k) = vDataOut
    Next 'n
    End With 'wksSource

    ...where I revised the code to be better self-documenting about vTmp. As
    shown, all other source data will be processed by code in the 'Else'
    part where vTmp is a single value.

    Output to the target sheet happens outside the 'If' because it's the
    same for all data.

    HTH with getting you going in the right direction!

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, May 3, 2014
    #9
  10. L. Howard

    GS Guest

    Typo fix...

    > You need to enter your InputBox values same as they appear in the
    > constants, which is a delimited list. So when the user types...
    >
    > C,F,P,S,D:E,R,M,Y,AA,BM
    >
    > ..into the InputBox, intellisense shows it as a string same as
    > exampled in the constants.
    >
    > You need to change the name of the var receiving the InputBox value
    > because, as shown, it's a duplicate declaration...
    >
    > Dim vSrcCols, vTgtCols, vTmp, n&
    > Dim vSrcCols As String
    >
    > ..where the 1st line types the 1st 3 vars as 'Variant', hence the
    > type prefix "v" in the name. Var n is type 'Long' as indicated by the
    > 'type symbol' appended to the name.
    >
    > The 2nd line re-declares vSrcCols as type 'String' and so this sub
    > won't even run because VBA will throw an exception (a.k.a raise an


    error). The point is its prefix suggests its type is 'Variant' same

    > as those in the 1st line. I normally use a 'Variant' for prompted
    > responses because the return type can vary depending on the vehicle
    > used. What's consistent about this approach is how I understand my
    > code EVERY TIME I prompt for user input...
    >
    > Dim vRet, vAns
    > vRet = InputBox...
    > ..followed by use-appropriate validation code
    >
    > vAns = MsgBox...
    > ..followed by use-appropriate validation code
    >
    > Since you're using VBA's InputBox() you're forcing VBA to coerse what
    > gets entered to type 'String'. I suggest you use Excel's InputBox()
    > so you can specify data type and save yourself the extra validation
    > (not apparent here!) coding required to make sure it's good type.
    >
    > Finally, it's a good idea to write down in plain english what the
    > 'intended' logic of your code is. That will cue you as to how to
    > write the code. Note also that since BOTH source/target col label
    > lists are 'in syn', you can manage them both in the same For..Next
    > loop because their indexes exactly match.
    >
    > This next bit of code...
    >
    >
    > '/** this needs to start at C4
    > Range("C" & k).Resize(UBound(MyArray) + 1, 1) = _
    > Application.Transpose(MyArray)
    >
    > ..is in the wrong place because:
    > it's 'output' code
    >
    > AND if vTmp is an array then it needs to process source data
    > as a range.
    >
    > This is where you will join the contents of D:E for
    > output to G in the target sheet...
    >
    > With wksSource
    > For n = LBound(vSrcCols) To UBound(vSrcCols)
    > vTmp = Split(vSrcCols(n), ":")
    > If IsArray(vTmp) Then
    > vDataOut = .Range(vTmp(0) & k).Value & " " _
    > & .Range(vTmp(1) & k).Value
    > Else
    > vDataOut = .Range(vTmp & k).Value
    > End If
    >
    > wksTarget.Range(vTgtCols(n) & k) = vDataOut
    > Next 'n
    > End With 'wksSource
    >
    > ..where I revised the code to be better self-documenting about vTmp.
    > As shown, all other source data will be processed by code in the
    > 'Else' part where vTmp is a single value.
    >
    > Output to the target sheet happens outside the 'If' because it's the
    > same for all data.
    >
    > HTH with getting you going in the right direction!


    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, May 3, 2014
    #10
  11. L. Howard

    L. Howard Guest

    I think I have confused you or I am plainly not understanding.

    The intent is to enter an ID number in the InputBox and then the code will look for that ID number in a column range.

    When it finds the number then I want to return the values in columns C,F,P,S,D:E,R,M,Y,AA,BM of that row to a column on another sheet.

    So, if the columns were not in a jumbled order I would use something like:

    c.Offset(,1).Resize(1,11). Copy ...Destination

    (Noting of course even that does not put D:E in the same cell, nor does it account for cells that need to be omitted either or the jumbled order.)

    That is the method I wanted to emulate like using an array similar to when you list the sheets in an array, where you can alter the sequence or omit sheets etc.

    Does that make sense?







    You need to enter your InputBox values same as they appear in the
    constants, which is a delimited list. So when the user types...

    C,F,P,S,D:E,R,M,Y,AA,BM
     
    L. Howard, May 3, 2014
    #11
  12. L. Howard

    GS Guest

    > I think I have confused you or I am plainly not understanding.

    I suspect the latter as my understanding was that you want to copy
    randomly loacated data from a specific row on wksSource and put that
    data in 'related' cells on wksTarget. That's what the loop does
    exactly!
    >
    > The intent is to enter an ID number in the InputBox and then the code
    > will look for that ID number in a column range.


    So you're saying the value of k on the source sheet should be the row
    of the found ID. If correct then what's the problem? Assign the
    'Find()' row to the variable used to pull the data from wksSource.

    If the target row is not the same row then use a diff var for that.
    >
    > When it finds the number then I want to return the values in columns
    > C,F,P,S,D:E,R,M,Y,AA,BM of that row to a column on another sheet.
    >
    > So, if the columns were not in a jumbled order I would use something
    > like:



    If you need to have the col labels NOT in constants then use vars and
    load them however you want at runtime. The code sample will reliably
    put data from wksSource correctly into wksTarget. You need to determine
    what those values are by making sure the source data is where it should
    be.

    >
    > c.Offset(,1).Resize(1,11). Copy ...Destination
    >
    > (Noting of course even that does not put D:E in the same cell, nor
    > does it account for cells that need to be omitted either or the
    > jumbled order.)
    >
    > That is the method I wanted to emulate like using an array similar to
    > when you list the sheets in an array, where you can alter the
    > sequence or omit sheets etc.
    >

    That's exactly what the constants do. The exact same approach is being
    used here as with the sheets array, just in a different context.
    'Split'ing them into variants creates the arrays such that are used in
    the loop code. Since both constants have identical elements listed in
    an associated order, working with them is simplified.

    It could also be done in random order using an 'index map' that lists
    array indexes in the desired order so, for example, if you need to have
    the data from D:E processed before any other data then '5' would be the
    1st element in the index map. This makes things slightly more complex,
    though, but is still simple to do...

    vTmp = Split(vSrcCols(vMap(n), ":")

    ...so the col label gets processed like so...

    wksTarget.Range(vTgtCols(vMap(n) & k) = vDataOut

    ...which doesn't disturb the structure of your constants. This allows
    more flexibility in that the elements for vMap can be grabbed at
    runtime by whatever means suits you (ie: from a cell, via InputBox,
    from a file)!

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, May 3, 2014
    #12
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.