I would like to check a range of cells and if I find a specific number copy a corresponding cell into a different cell. Example check I:3 to I:33 and 100 in I:6, I:9 and I:24. I would like to copy what is in corresponding cells A:6, A:9 and A;24 into a new cell

✔ Best Answer

Your Rows are numbered incorrectly. You have no Row number for your Column headings, so I'll assume that your data actually starts in A2, not A1. That shifts everything down by 1 row.

I'll work on something.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

First, just some notation comments so that your posts are easier to read: Single cells should be written as: I3, A24, etc. (ColumnRow)

A Range of cells should be written as: I3:I33 (FristCell:LastCell)

A Range of Rows should be written as 4:24

A Range of Columns should be written as: D:HTry this:

In J3 enter this formula and drag it down to J33:

=IF(I3 = 100, A3, "")

If I3 contains 100, the value in A3 will appear in J3. If I3 does not contain 100, J3 will be blank.

Dragging it down will repeat the formula for each Row.

Put this in

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thanks for the info

but the end result I would like is if I3, I5 and I9 contains 100 and A3, contains 101 and A5 contains 104 and A9 contains 108 then I would like J3 results to be 101,104 108

If I understand what you are trying to do, you are going to need to VBA (a macro). You gave an example of three cells in your range containing 100. Is it safe to assume that there could be two 100's or four 100's or twenty 100's, etc.? For each 100 in I3:I33, the corresponding value in Column A needs to be part of the string in J3, right?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Correct

And J4 would be 101, J5 would be 102 ect.

I have no idea what you mean by your latest response. Since a macro has to be very specific in the actions it performs, we would need to know exactly what you want done. "etc" doesn't give us a whole bunch of detail.

Perhaps it would help us if you posted a subset of your data, both the input and the desired output, so that we can get a better understanding of your requirements.

If you are going to post data, please click on the following blue sentence and read the instructions on how to post data in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

A B C D E Team name amt bidder# 1 101 Jim $100 101 2 102 Ken $200 102 3 103 Joe $600 101 4 104 Dan $300 101 5 105 Tom $100 105 6 106 Sue $100 106The results I would want in E1 would be

101,103,104. E2 would be 102 and E3 would be

would be blank E4 blank E5 would be 105

and E6 would be 106. So whenever I find

101 in a D cell I would show the list of the numbers from the A cells corresponding to it

in E1 same for 102 ect. There could be up to 200

teams.

Your Rows are numbered incorrectly. You have no Row number for your Column headings, so I'll assume that your data actually starts in A2, not A1. That shifts everything down by 1 row.

I'll work on something.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Option Explicit Sub CopyDataFromColA() Dim lastRw, srcRw, dstRw As Integer 'Clear Column E prior to populating Columns("E:E").ClearContents 'Deterime last row in Column D lastRw = Range("D" & Rows.Count).End(xlUp).Row 'Initialize Source Row for Range srcRw = 2 nxtSrcRw: 'Loop through Range For dstRw = srcRw To lastRw 'Check to see if Source value has already been used If WorksheetFunction.CountIf _ (Range(Cells(2, "D"), Cells(srcRw, "D")), Cells(srcRw, "D")) > 1 _ Then GoTo inc_srcRw 'If not, build temporary string If Cells(dstRw, "D") = Cells(srcRw, "D") Then Cells(srcRw, "E") = Cells(srcRw, "E") & Cells(dstRw, "A") & ", " End If Next 'Strip off last comma after temporary string is complete Cells(srcRw, "E") = Left(Cells(srcRw, "E"), Len(Cells(srcRw, "E")) - 2) inc_srcRw: 'Increment Source row for range srcRw = srcRw + 1 'Quit if we've checked all source Rows If srcRw > lastRw Then Exit Sub 'Check new range if not done GoTo nxtSrcRw End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History