Table Update

[phil2006]

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

 

[Guest]

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.

 

[Dave Peterson]

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]

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

 

[Dave Peterson]

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]

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