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

[L. Howard]

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

 

[GS]

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

 

[L. Howard]

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

 

[GS]

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

 

[L. Howard]

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]

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

 

[GS]

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

 

[L. Howard]

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

 

[GS]

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]

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

 

[L. Howard]

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

 

[GS]

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