How to find a number manually entered in a cell and then copy and paste it to another cell

[Mike Guest98]

Hi


I’m a newbie to Excel and have spent many hours with no luck on this one. I’m trying to find the location of a manually entered number, 1 to 10 is the choice. So the number gets entered in cell C17, say number 4 as an example.

Using VBA I need it to search the following 5 column locations: C30 TO C50, E30 TO E50, G30 TO G50, H30 TO H50 AND L30 TO L50. Each column has a number 4 but only one 4 has a value in the cell to the right of it. So as an example IF it finds 4 in C30 and a value in D30 it is a positive (if their was no value in D30 it would be negative).

Now I need the value in cell D30 to be copied and pasted in cell F10.

The end

Thanks in a advance for any help.
Mike

<style type="text/css">p { margin-bottom: 0.1in; line-height: 115%; }</style>

 

[offthelip]

Try this it shoulkd get you started but it is untested:

Sub test()
Dim colno(1 To 5) As Long
colno(1) = 3
colno(2) = 5
colno(3) = 7
colno(4) = 8
colno(5) = 12


inarr = Range(Cells(1, 1), Cells(50, 13))
 chkno = Cells(17, 3)
 fnd = False
 For i = 1 To 5
   For j = 30 To 50
     If (inarr(j, colno(i)) = chkno) And (inarr(j, colno(i) + 1) <> "") Then
       Cells(10, 6) = inarr(j, colno(i) + 1)
       fnd = True
       Exit For
     End If
   Next j
  If fnd Then Exit For
 Next i
 
End Sub

 

[Mike Guest98]

Thanks so much for your help. I would have never come up with this one.

 

[Mike Guest98]

It didn't work when I tested the answer so rewrote the problem.


I’m a newbie to Excel and have spent many hours with no luck on this one. Using actual cell locations here is the project:


1) In cell C17 I enter one number, can any number from 1 to 10. Lets say I choose the number 4 as an example.


2) I have 5 locations where number 4 could be located: C27 TO C36, F27 TO F36, I27 TO I36, L27 TO L36 AND O27 TO O36. Note each column has a number 4 but only one has a value in the cell to the right of it. So as an example IF it finds a 4 in cell F27 and a value such as 1-1 in F28 it is a positive (if their was no value in F28 it would be negative).


3) If it’s a positive (a value in cell F28) I need it to copy and paste the 1-1 in cell J3.



The end

Thanks in advance for any help you can provide.

Mike
<style type="text/css">p { margin-bottom: 0.1in; direction: ltr; color: rgb(0, 0, 10); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }</style>
<style type="text/css">p { margin-bottom: 0.1in; direction: ltr; color: rgb(0, 0, 10); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }</style>

 

[Eric W]

Put this formula in J3:

=IFERROR(INDIRECT(TEXT(MIN(IF(MOD(COLUMN(C27:Q36),3)=0,IF(C27:Q36=C17,IF(D27:R36<>"",ROW(D27:R36)*100+COLUMN(D27:R36))))),"R00C00"),0),"")

confirm it by pressing Control+Shift+Enter.