Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Table Update
If I have the following table (sheet1): A 1 2 3 4 5 B 4 5 6 7 8 C 1 9 0 3 7 ...and then (sheet2) A 1 B 1 A 2 C 6 B 2 A 3 ...is there a macro or programme I could run which would insert th values from sheet 2 in order into the rows on sheet one to give: A 1 2 3 1 2 3 4 5 B 1 2 4 5 6 7 8 C 6 1 9 0 3 7 Thanks very much -- phil200 ----------------------------------------------------------------------- phil2006's Profile: http://www.excelforum.com/member.php...fo&userid=3509 View this thread: http://www.excelforum.com/showthread.php?threadid=54954 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Table Update
Sub ABC()
Dim sh As Worksheet Dim rng1 As Range, rng2 As Range Dim cell As Range, i As Long Dim j As Long, v As Variant Set sh = Worksheets("Sheet1") With Worksheets("Sheet1") Set rng1 = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)) End With With Worksheets("Sheet2") Set rng2 = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)) End With v = rng1.Resize(, 2) For i = LBound(v) To UBound(v) v(i, 2) = 2 Next For Each cell In rng2 For i = LBound(v) To UBound(v) If v(i, 1) = cell.Value Then sh.Cells(i, v(i, 2)).Insert xlShiftToRight sh.Cells(i, v(i, 2)).Value = cell.Offset(0, 1).Value v(i, 2) = v(i, 2) + 1 Exit For End If Next Next End Sub Produced what you show. My data started in A1 of each sheet. -- Regards, Tom Ogilvy "phil2006" wrote: If I have the following table (sheet1): A 1 2 3 4 5 B 4 5 6 7 8 C 1 9 0 3 7 ...and then (sheet2) A 1 B 1 A 2 C 6 B 2 A 3 ...is there a macro or programme I could run which would insert the values from sheet 2 in order into the rows on sheet one to give: A 1 2 3 1 2 3 4 5 B 1 2 4 5 6 7 8 C 6 1 9 0 3 7 Thanks very much! -- phil2006 ------------------------------------------------------------------------ phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092 View this thread: http://www.excelforum.com/showthread...hreadid=549543 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Table Update
This worked ok for me. But it overwrites the original sheet1. So save your
work before you start. Close the workbook without saving if it's not right: Option Explicit Sub testme() Dim wks1 As Worksheet Dim wks2 As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim res As Variant Set wks1 = Worksheets("sheet1") Set wks2 = Worksheets("sheet2") With wks2 FirstRow = 1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 res = Application.Match(.Cells(iRow, "A").Value, _ wks1.Range("a:a"), 0) If IsError(res) Then MsgBox "error with row#: " & iRow Exit Sub End If wks1.Cells(res, 2).Insert Shift:=xlToRight wks1.Cells(res, 2).Value = .Cells(iRow, "B").Value Next iRow End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm phil2006 wrote: If I have the following table (sheet1): A 1 2 3 4 5 B 4 5 6 7 8 C 1 9 0 3 7 ..and then (sheet2) A 1 B 1 A 2 C 6 B 2 A 3 ..is there a macro or programme I could run which would insert the values from sheet 2 in order into the rows on sheet one to give: A 1 2 3 1 2 3 4 5 B 1 2 4 5 6 7 8 C 6 1 9 0 3 7 Thanks very much! -- phil2006 ------------------------------------------------------------------------ phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092 View this thread: http://www.excelforum.com/showthread...hreadid=549543 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Table Update
Thanks very much, that's really helped! If my data on sheet two is i column 3 how do I change the formula? Thanks agai -- phil200 ----------------------------------------------------------------------- phil2006's Profile: http://www.excelforum.com/member.php...fo&userid=3509 View this thread: http://www.excelforum.com/showthread.php?threadid=54954 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Table Update
There could be up to 3 spots to make changes.
Look for the arrows and the line under that question line. Option Explicit Sub testme() Dim wks1 As Worksheet Dim wks2 As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim res As Variant Set wks1 = Worksheets("sheet1") Set wks2 = Worksheets("sheet2") With wks2 FirstRow = 1 'look at Column C to get the last row? <---- LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 'and use the value in column C <----- res = Application.Match(.Cells(iRow, "C").Value, _ wks1.Range("a:a"), 0) If IsError(res) Then MsgBox "error with row#: " & iRow Exit Sub End If wks1.Cells(res, 2).Insert Shift:=xlToRight 'what column should be brought back to wks1??? <---- wks1.Cells(res, 2).Value = .Cells(iRow, "B").Value Next iRow End With End Sub phil2006 wrote: Thanks very much, that's really helped! If my data on sheet two is in column 3 how do I change the formula? Thanks again -- phil2006 ------------------------------------------------------------------------ phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092 View this thread: http://www.excelforum.com/showthread...hreadid=549543 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Table Update
Thanks again. This is starting to work really well, is there any wa that if I have data in two tables on sheet two I can have a simila process which inserts corresponding data in order? e.g. (sheet 2) A 1 B 5 B 3 A 2 A 3 B 1 Producing the table: (sheet 1) A 1 2 3 B 5 3 1 Thanks again for your help. Phi -- phil200 ----------------------------------------------------------------------- phil2006's Profile: http://www.excelforum.com/member.php...fo&userid=3509 View this thread: http://www.excelforum.com/showthread.php?threadid=54954 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Update | Excel Discussion (Misc queries) | |||
Update Table or Reorder | Excel Discussion (Misc queries) | |||
Pivot Table update | Excel Discussion (Misc queries) | |||
VBA to update Pivot table | Excel Programming | |||
PLEASE HELP...How do I update a pivot table with VBA? | Excel Programming |