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

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

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

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

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

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