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

Report •


✔ Best Answer
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:24
A Range of Columns should be written as: D:H

Try 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 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

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
Correct
And 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 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.


Report •

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


Report •

#8
August 12, 2011 at 11:05:34
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.


Report •

Ask Question