Hi, I wanted to modify this program in accordance with example below
As an example;
in sheet1 I have following columns:-
A----B ---C---D----E---F---G---H---I---J---K---L
ID---Name---Date---Type---Status---Reasons--- Date2---Status2---Status3---Date3---Name2---Name3
data1 a r f q e r
data2 q j l
data3 y p o e m s
data4 n s t 4
In sheet 2 I have
data1 a r f <empty> <empty> <empty>
data3 y p o <empty> <empty>
data5 d q t
data7 q o p l
and how can I make this modification in sheet2 itself:
data1 a r f q e r
data2 q j l
data3 y p o e m s
data4 n s t y
data5 d q t
data7 q o p l
It is important that to update data in remaining columns of Sheet2, Col A, C and D must be exactly same in both Sheet1 and Sheet2.
Including Column B can create problem.
<empty> refers to a Blank cell.
Here, data4 will have conflict in column E. Then, how can I ask User to select either Sheet1 or Sheet2?
[VBA]
Sub modifysheets()
Dim ws1, ws2, ws3 As Worksheet
Dim firstrow1, lastrow1, firstrow2, lastrow2, firstrow3, lastrow3 As Integer
Dim lastcol1, lastcol2, lastcol3 As Integer
Dim r1, r2, r3, c1, c2, c3 As Integer
Dim data_found As Boolean
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set ws3 = Worksheets("Sheet3")
firstrow1 = 1
lastrow1 = ws1.Cells(firstrow1, "A").End(xlDown).Row
firstrow2 = 1
lastrow2 = ws2.Cells(firstrow2, "A").End(xlDown).Row
firstrow3 = 1
lastrow3 = 0
ws3.Cells.Clear
For r1 = firstrow1 To lastrow1
lastcol1 = ws1.Cells(r1, "A").End(xlToRight).Column
For c3 = 1 To lastcol1
ws3.Cells(firstrow3 + r1 - firstrow1, c3).Value = ws1.Cells(r1, c3).Value
Next c3
lastcol3 = lastcol1
For r2 = firstrow2 To lastrow2
If ws2.Cells(r2, "A").Value = ws1.Cells(r1, "A").Value Then
lastcol2 = ws2.Cells(r2, "A").End(xlToRight).Column
For c2 = 2 To lastcol2
data_found = False
For c1 = 1 To lastcol1
If ws1.Cells(r1, c1).Value = ws2.Cells(r2, c2).Value Then
data_found = True
Exit For
End If
Next c1
If Not data_found Then
lastcol3 = lastcol3 + 1
ws3.Cells(firstrow3 + r1 - firstrow1, lastcol3).Value = ws2.Cells(r2, c2)
End If
Next c2
End If
Next r2
Next r1
lastrow3 = lastrow1 - firstrow1 + firstrow3
For r2 = firstrow2 To lastrow2
data_found = False
For r1 = firstrow1 To lastrow1
If ws1.Cells(r1, "A").Value = ws2.Cells(r2, "A").Value Then
data_found = True
End If
Next r1
If Not data_found Then
lastrow3 = lastrow3 + 1
lastcol2 = ws2.Cells(r2, "A").End(xlToRight).Column
For c2 = 1 To lastcol2
ws3.Cells(lastrow3, c2).Value = ws2.Cells(r2, c2).Value
Next c2
End If
Next r2
End Sub
[/VBA]
Reply With Quote