Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving data from one sheet to a template, matching numbers
Hello,
I need help trying to get data from sheet 1 into sheet 2, in the right spot. Here's what my data looks like: (sheet 1) D 240102 x 90 200 D 240103 y 9787 100 D 240107 z 8897 200 D 240110 a 45 640 (blank row) S 240229 j 4545 100 S 240232 k 544545 200 S 240233 l 454556 124 S 240238 m 45456 135 (blank row) And here's the template where it needs to go (only the numbers in columns 4 and 5 need to be filled in underneath each other for the matching 240xxx number, the template with the numbers in column B is already filled in) (sheet 2) D 240101 D 240102 x 90 200 D 240103 y 9787 100 D 240104 D 240105 D 240106 D 240107 z 8897 200 D 240108 D 240109 D 240110 a 45 640 D 240111 (blank row) S 240229 j 4545 100 S 240230 S 240231 S 240232 k 544545 200 S 240233 l 454556 124 S 240234 S 240236 S 240237 S 240238 m 45456 135 Possible ranges are 240101 - 240199 and 240229 - 240285. Any help would be greatly appreciated!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving data from one sheet to a template, matching numbers
Try this code Sub MoveData() With Sheets("Sheet1") LastRow = .Range("A" & Rows.Count).End(xlUp).Row RowCount = 1 Do While RowCount <= LastRow ID = .Range("B" & RowCount) If ID < "" Then Num1 = .Range("D" & RowCount) Num2 = .Range("E" & RowCount) With Sheets("Sheet2") Set c = .Columns("B").Find(what:=ID, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find ID : " & ID) Else Range("D" & c.Row) = Num1 Range("A" & (c.Row + 1)) = Num2 End If End With End If RowCount = RowCount + 1 Loop End With End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165075 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving data from one sheet to a template, matching numbers
That works like a charm, thank you!!!!
Now let me take it one step further. Say this scenario is repeated 10 times (for 10 different branches, with different numbers for each branch). So on sheet 1 there is a column A which states the branch (1-10). On sheet 2, the template is repeated 10 times with in column A the branch number. How do you build in a loop that compares the branch number on sheet 1 to the branch number in sheet 2, and repeats the macro for each branch? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving data from one sheet to a template, matching numbers
I modified the code to look for every occurance of the ID number in column B on sheet 2 Sub MoveData() With Sheets("Sheet1") LastRow = .Range("A" & Rows.Count).End(xlUp).Row RowCount = 1 Do While RowCount <= LastRow ID = .Range("B" & RowCount) If ID < "" Then Num1 = .Range("D" & RowCount) Num2 = .Range("E" & RowCount) With Sheets("Sheet2") Set c = .Columns("B").Find(what:=ID, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find ID : " & ID) Else FirstAddr = c.Address Do Range("D" & c.Row) = Num1 Range("A" & (c.Row + 1)) = Num2 Set c = .Columns("B").FindNext(after:=c) Loop While Not c Is Nothing And c.Address < FirstAddr End If End With End If RowCount = RowCount + 1 Loop End With End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165075 Microsoft Office Help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving data from one sheet to a template, matching numbers
That was not completely what I meant. Here's how sheet 1 looks now
with two branches: (sheet 1) 1 D 240102 x 90 200 1 D 240103 y 9787 100 1 D 240107 z 8897 200 1 D 240110 a 45 640 (blank row) 1 S 240229 j 4545 100 1 S 240232 k 544545 200 1 S 240233 l 454556 124 1 S 240238 m 45456 135 (blank row) This now gets moved to sheet 2 as above but with an additional column A that has the branch 1 and 2 2 D 240101 x 40 200 2 D 240105 y 87 400 2 D 240107 z 8897 200 2 D 240110 a 45 640 (blank row) 2 S 240229 j 4545 100 2 S 240232 k 544545 200 2 S 240233 l 454556 124 2 S 240240 m 4 35 (blank row) (sheet 2) 1 D 240101 x 40 200 1 D 240102 1 D 240103 y 9787 100 1 D 240104 1 D 240105 1 D 240106 1 D 240107 z 8897 200 1 D 240108 1 D 240109 1 D 240110 a 45 640 1 D 240111 (blank row) 1 S 240229 j 4545 100 1 S 240230 1 S 240231 1 S 240232 k 544545 200 1 S 240233 l 454556 124 1 S 240234 1 S 240236 1 S 240237 1 S 240238 m 45456 135 2 D 240101 2 D 240102 x 90 200 2 D 240103 y 9787 100 2 D 240104 2 D 240105 2 D 240106 2 D 240107 z 8897 200 2 D 240108 2 D 240109 2 D 240110 a 45 640 2 D 240111 (blank row) 2 S 240229 j 4545 100 2 S 240230 2 S 240231 2 S 240232 k 544545 200 2 S 240233 l 454556 124 2 S 240234 2 S 240236 2 S 240237 2 S 240238 m 45456 135 2 S 240239 2 S 240240 4 35 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving data from one sheet to a template, matching numbers
I think futher explanation is needed. What do you mean by an additional column A. A worksheet cannot have two column A's. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165075 Microsoft Office Help |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving data from one sheet to a template, matching numbers
Sorry if that was unclear. I meant that we are have now extended our
original project by inserting a column A that has the branch number. This column was inserted both in sheet 1 and sheet 2. If branch=1 in sheet 1, it is the same in sheet 2. So it goes through branch 1 and does our original project, then goes through branch 2 and does the same thing, all the way down. Hopefully that makes more sense? Thanks again for helping me with this! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Verifying and moving non matching numbers in columns | Excel Worksheet Functions | |||
Matching and Moving Data From One Spreadsheet to Another? | Excel Discussion (Misc queries) | |||
Matching and Moving Data From One Spreadsheet to Another? | Excel Programming | |||
Matching and Moving Data From One Spreadsheet to Another? | Excel Discussion (Misc queries) | |||
Matching and Moving Data From One Spreadsheet to Another? | Excel Programming |