# Solved copy a number from a range of cells

August 9, 2011 at 10:22:38
Specs: Windows XP
 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

See More: copy a number from a range of cells

August 12, 2011 at 10:05:41
 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.

#1
August 9, 2011 at 12:15:37
 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:24A 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.

Report •

#2
August 10, 2011 at 09:43:00
 Thanks for the infobut 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

Report •

#3
August 10, 2011 at 18:06:02
 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.

Report •

Related Solutions

#4
August 11, 2011 at 08:22:31
 CorrectAnd J4 would be 101, J5 would be 102 ect.

Report •

#5
August 11, 2011 at 13:43:23
 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.

Report •

#6
August 12, 2011 at 09:54:31
 ``` 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 106```The results I would want in E1 would be101,103,104. E2 would be 102 and E3 would bewould 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 itin E1 same for 102 ect. There could be up to 200teams.

Report •

#7
August 12, 2011 at 10:05:41
 ```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.