Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locate number within range, copy number below it to another cell
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locate number within range, copy number below it to another cell
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 Chickering "A good example is twice the value of good advice." " wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locate number within range, copy number below it to another cell
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locate number within range, copy number below it to another cell
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Skip over 1st number and locate 2nd - ? | Excel Discussion (Misc queries) | |||
Locate negative number | Excel Worksheet Functions | |||
VBA code to locate the command bar number the printer button is on | Excel Programming | |||
Locate first number that exceeds reference value | Excel Worksheet Functions | |||
Copy a number from one cell to another depending on first number | Excel Worksheet Functions |