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

Giganews Newsgroups
Subject: Array with many cells from a row but not in order or together
Posted by:  L. Howard (lhkitt…@comcast.net)
Date: Thu, 1 May 2014

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

Replies