Locate number within range, copy number below it to another cell

  • Thread starter Thread starter acctemp
  • Start date Start date
A

acctemp

Hi,

I have a range of numbers and want Excel to locate a specific number
then go down one cell to copy the number from there and the three cells
to the right of it to another location. I then need to repeat this
operation about 45 times. All data is on the same worksheet. For
example:


Locate number 101 and then copy number immediately below it (65.89) and
the three numbers to the right of it (112.44, 875.42, 695.84, to Cells
A100-D100.

A B C D

1 100
2 35.28 67.28 96.85 135.42
3 101
4 65.89 112.44 875.42 695.84
5 102
6 750.20 68.57 41.04 74.52

Any simple way to do this? Thanks in advance!
 
Sub FindCopy()
Dim r As Range
Dim fR As Range 'First Range
Set r = Cells.Find(What:="101", After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
If r Is Nothing Then Exit Sub
Set fR = r
Do
If Range("A100") = "" Then
r.Offset(1).Resize(1,4).Copy Range("A100")
Else
r.offset(1).Resize(1,4).Copy Range("A" &
Rows.Count).End(xlUp).Offset(1)
End If
Set r = Cells.FindNext(fR)
Loop Until r.Address = fR.Address

End Sub
 
Charles,

Thanks for the code, I got it to work on the example but of course on
the real life spreadsheet it didn't, sigh. When I ran the code it
would calculate non-stop and I had to end the program. So much for
shortcuts in explaining what I needed. The four numbers are in the
same position as noted before, however the input/output ranges are
different:

Within Range A:129:E400 look for the following numbers (not within a
number but the exact number itself, e.g., 1010102.15 contains 101 but
should not be counted, only "101" without any digits before or after
applies):

101 - Output Four Numbers Below to Range B5:E5
102 - Output Four Numbers Below to Range B6:E6
103 - Output Four Numbers Below to Range B7:E7
104 - Output Four Numbers Below to Range B8:E8
105 - Output Four Numbers Below to Range B9:E9
106 - Output Four Numbers Below to Range B10:E10
107 - Output Four Numbers Below to Range B11:E11
108 - Output Four Numbers Below to Range B12:E12

201 - Output Four Numbers Below to Range B22:E22
202 - Output Four Numbers Below to Range B23:E23
203 - Output Four Numbers Below to Range B24:E24
204 - Output Four Numbers Below to Range B25:E25
205 - Output Four Numbers Below to Range B26:E26
206 - Output Four Numbers Below to Range B27:E27

301 - Output Four Numbers Below to Range B37:E37
302 - Output Four Numbers Below to Range B38:E38
303 - Output Four Numbers Below to Range B39:E39
304 - Output Four Numbers Below to Range B40:E40
305 - Output Four Numbers Below to Range B41:E41
306 - Output Four Numbers Below to Range B42:E42
307 - Output Four Numbers Below to Range B42:E42

401 - Output Four Numbers Below to Range B53:E53
402 - Output Four Numbers Below to Range B54:E54
403 - Output Four Numbers Below to Range B55:E55
404 - Output Four Numbers Below to Range B56:E56
405 - Output Four Numbers Below to Range B57:E57
406 - Output Four Numbers Below to Range B58:E58

501 - Output Four Numbers Below to Range B68:E68
502 - Output Four Numbers Below to Range B69:E69
503 - Output Four Numbers Below to Range B70:E70
504 - Output Four Numbers Below to Range B71:E71
505 - Output Four Numbers Below to Range B72:E72
506 - Output Four Numbers Below to Range B73:E73

601 - Output Four Numbers Below to Range B83:E83
602 - Output Four Numbers Below to Range B84:E84
603 - Output Four Numbers Below to Range B85:E85
604 - Output Four Numbers Below to Range B86:E86
605 - Output Four Numbers Below to Range B87:E87

701 - Output Four Numbers Below to Range B100:E100
702 - Output Four Numbers Below to Range B101:E101
703 - Output Four Numbers Below to Range B102:E102
704 - Output Four Numbers Below to Range B103:E103
705 - Output Four Numbers Below to Range B104:E104
706 - Output Four Numbers Below to Range B105:E105

Thanks for the help, I really appreciate it!
 
Charles,

Managed to use your code although in a ugly fashion (44 command buttons
which 1 button clicks on, lol) but at least it works! Thanks!
 
Back
Top